I have two tables with primary clustered keys by id. Also I have stored procedure The Execution plan shows me a ‘Clustered Index Scan’ that I want to avoid. Is there any way I can convert it to a ‘Clustered Index Seek’? Execution Plan screen <db fiddle> Answer As I mentioned in t…
Tag: sql-execution-plan
Performance impact of view on aggregate function vs result set limiting
The problem Using PostgreSQL 13, I ran into a performance issue selecting the highest id from a view that joins two tables, depending on the select statement I execute. Here’s a sample setup: What I found out I’m executing two statements which result in completely different execution plans and run…
SQL Server Views | Inline View Expansion Guidelines
Background Hello all! I recently learned that in newer versions of SQL Server, the query optimizer can “expand” a SQL view and utilize inline performance benefits. This could have some drastic effects going forward on what kinds of database objects I create and why and when I create them, dependin…
How do I process execution plan in PostgreSQL?
In PostgreSQL, when I run: I get: How can I get the “Total Cost” from there? I tried: But I get the error: Answer explain always needs to go before a statement, you can’t put it in the middle of one. You can do what you want by wrapping this into a function: If you can’t use a function…
Condition within aggregate query vs math
I have these two query examples with small difference that i thought would be performance optimization but there is no difference. The small change is that in one of the queries there is conditional logic within the aggregate while in the other one i use simple math to get the same result. I would have though…
Ideal Postgres Index For Json Data With Integer Timestamp
I have millions of records in this table using Amazon Aurora Postgres 10.7: Example row: {“id”: “abc”, “ts”: 1580879910, “data”: “my stuff”} I have these queries that take dozens of seconds: I’m trying to improve performance here, and these are…
Why does this simple query not use the index in postgres?
In my postgreSQL database I have a table named “product”. In this table I have a column named “date_touched” with type timestamp. I created a simple btree index on this column. This is the schema of my table (I omitted irrelevant column & index definitions): The table has ~300,000 …
Meaning of “Select tables optimized away” in MySQL Explain plan
What is the meaning of Select tables optimized away in MySQL Explain plan? explain select count(comment_count) from wp_posts; +—-+————-+—————————+———————…