Skip to main content
Share Your Experience: Take the 2024 Developer Survey
22 votes
Accepted

What's the order of operations in PostgreSQL?

Jasen's answer is correct - PostgreSQL is free to return the rows in any order it pleases unless you add an ORDER BY clause: (SELECT ... UNION SELECT ... UNION ALL SELECT ...) ORDER BY ...; But let ...
Laurenz Albe's user avatar
21 votes

What's the order of operations in PostgreSQL?

SQL makes no guarantee of result ordering unless you have an order by clause in your query. If you don't say "order by" your results will come in whatever order the query planner and ...
Jasen's user avatar
  • 3,593
11 votes
Accepted

Extra NESTED LOOP / INNER JOIN causing NO JOIN PREDICATE warning

The optimizer decided your query was a selective star query on fact and dimension tables. The giveaway is the presence of StarJoinInfo attributes on the plan's join operators (except the cartesian ...
Paul White's user avatar
  • 85.8k
8 votes
Accepted

Why sql server prefer index scan over the seek?

SQL Server chooses the plan that seems cheapest based on its estimates. That means the plan you have in mind, with an index seek on both tables, would be costed higher. You can test this for yourself ...
Paul White's user avatar
  • 85.8k
8 votes

Understanding and Optimizing Duplicate Query Hashes Across Multiple Databases in SQL Server

nope This is a problem with any multi-tenant solution, whether the separation is by database, or by schema. I'm not suggesting you intermix clients just for the sake of the plan cache, though. For ...
Erik Darling's user avatar
  • 41.1k
8 votes

Why is COMPATIBILITY_LEVEL: 160 using Clustered Index Scan?

The plans provided are taken with different database states at different points in time, or perhaps on different servers: In the hash join plan, Dokument has 16,478,000 rows and ExternalBookmark has ...
Paul White's user avatar
  • 85.8k
7 votes
Accepted

Getting a SORT operator when I have an index

The Id column in your temporary table is unique, but you're not telling the optimizer about that. Replace the existing nonclustered index on the temporary table with: CREATE UNIQUE CLUSTERED INDEX ...
Paul White's user avatar
  • 85.8k
7 votes

Execution slow in stored procedure; fast when run ad hoc

eh I have tried rewriting this to use local variables, optimize for unknown\variable\etc, with recompile. The first two methods listed here are pretty gosh-heckin' useless most of the time for ...
Erik Darling's user avatar
  • 41.1k
6 votes
Accepted

Partitioned view aggregation query not optimized

The SQL Server optimizer has two main ways to push an aggregate down past a union all. 1. Global Pushdown The first rule is GbAggBelowUniAll. It's a fairly straightforward transform that moves the ...
Paul White's user avatar
  • 85.8k
6 votes
Accepted

How to delay convert() evaluation until after join

If you want to use this self join approach I'd just use try_convert so you aren't dependent on where the convert gets evaluated. TRY_CONVERT(smallint, '37645') returns NULL rather than error so it ...
Martin Smith's user avatar
  • 84.8k
5 votes

Getting a SORT operator when I have an index

cluster it The main problem with your query plan is the use of a Batch Mode sort. The reason this drags you down so much is because unless they're a child operator of a Window Aggregate, all rows end ...
Erik Darling's user avatar
  • 41.1k
5 votes
Accepted

How does SQL Server optimize JOIN on hierarchyid::IsDescendantOf()?

descendant.Hieararchy.IsDescendantOf(ancestor.Hieararchy) = 1 certainly doesn't look like it should be sargable but it seems to do some shenanigans very early on in the process for this specific case. ...
Martin Smith's user avatar
  • 84.8k
5 votes
Accepted

Aside from explicitly flushing the cache or demanding recompiles, what recompiles a full stored procedure in SQL Server 2019?

not much Outside of what has been listed in your question, there is really no common generic cause for a stored procedure to always recompile a new plan for every statement, at least not for the one ...
Erik Darling's user avatar
  • 41.1k
5 votes

Why is my SQL Server prefering to pick one plan over the other when there are 2 executions plans for the same query in the cache?

Multiple plans Multiple plans (each with a unique plan_handle) for the same batch text (sql_handle) means one or more cache keys must be different. A difference in SET options is one possibility ...
Paul White's user avatar
  • 85.8k
4 votes
Accepted

Does PERCENTILE_CONT still give terrible performance when calculating medians in 2022 versions of SQL Server?

I presume one of the online sources you refer to is What is the fastest way to calculate the median? "2012_A" and "2012_B" below refer to queries from that article. Using similar ...
Martin Smith's user avatar
  • 84.8k
4 votes

In SQL Server, if I have two otherwise identical stored procedures with different names, will they use the same execution plan?

yeah, but... This will work, but now you have two pieces of code to maintain. It may be easier to use parametrized dynamic SQL within one stored procedure to do this. Using the Stack Overflow database ...
Erik Darling's user avatar
  • 41.1k
4 votes

Why force plan is not working with failure "NO_PLAN"?

Not every plan SQL Server can generate is capable of being forced. See Query Store plan force fails with NO_PLAN dependent on where filter operator is in plan. Maybe. Worth trying. Hard to speculate ...
Paul White's user avatar
  • 85.8k
4 votes
Accepted

Optimize query with many joins

In addition to various other possible issues (some of which have been addressed already) - most importantly server configuration and index optimization - there is an issue with joins. I count 9 join ...
Erwin Brandstetter's user avatar
3 votes

Why sql server prefer index scan over the seek?

SQL-Server used an index seek on ClosedTask on the State index (probably because this column is used in the condition) and an index scan on ClosedTaskCustomFields. If State was very selective, then ...
Olivier Jacot-Descombes's user avatar
3 votes
Accepted

query bad performance

yes Don't use substring in your where clause: All of the time in this query is spent seeking through 25,219,726 rows in SentencePartValues, then applying the filter/substring to LiteralValue. Of ...
Erik Darling's user avatar
  • 41.1k
3 votes
Accepted

Is WHERE [FOO] IN (SELECT [BAR] FROM [...]) guaranteed to give the same execution plan as the equivalent WHERE EXISTS?

The claim is still true, but it was only ever a concern for long hard-coded IN expression lists. The issue is primarily around the extra compilation time, although weird query plans can also result, ...
Charlieface's user avatar
  • 13.2k
3 votes
Accepted

Why can SQL Server not perform a TOP N SORT between an Index Seek and the Key Lookup?

I would like the primary purpose of this question to be WHY does this happen? Seems like a trivial operation thing to do the sort and top clause prior to the key lookup loop if you are able. There ...
Paul White's user avatar
  • 85.8k
3 votes

Why can SQL Server not perform a TOP N SORT between an Index Seek and the Key Lookup?

self-love You can probably get the plan shape you're after by doing a self-join, which is far more easily done in Entity Framework than all the CTE stuff. It's also a more reliable option because CTEs ...
Erik Darling's user avatar
  • 41.1k
3 votes
Accepted

Why index scan can be slow in Postgres?

The index scan is taking a long time because it is repeated very often there are not enough index columns, so PostgreSQL has to fetch the table rows. Try this index: CREATE INDEX ON shipment (...
Laurenz Albe's user avatar
3 votes
Accepted

optimize SELECT MAX() with GROUP BY in Postgres?

Unfortunately postgres does not implement (yet) the index scan type you need to optimize this query automatically, so it will scan the whole index. It is capable of using an index on (a,b) to optimize ...
bobflux's user avatar
  • 1,511
3 votes
Accepted

Index seeks and predicate push down

Solution You can use an expanded FORCESEEK hint with the seek keys needed: FROM dbo.MyTable AS M WITH (FORCESEEK(IDX(WTID, OETID, SRID))) For example: IF EXISTS ( SELECT T.* FROM ( ...
Paul White's user avatar
  • 85.8k
2 votes
Accepted

Why is the PostgreSQL Query Planner choosing such an inefficient solution?

The planner thinks that the rows it finds meeting the conditions will be randomly distributed along the tasks.id ordering. So by finding them already in order it thinks it can stop after the first ...
jjanes's user avatar
  • 40.1k
2 votes

Is it a problem if BIGINT data type parameter gets passed into a table for join with INT data type?

Is it a problem if BIGINT data type parameter gets passed into a table for join with INT data type? It can be, from a performance perspective, yes. When there are different data types being compared ...
J.D.'s user avatar
  • 37.9k
2 votes

Temp table with less number of rows results in Clustered Index Seek Whereas more rows result in Clustered Index Scan - SQL Server

As Andy mentioned, there's nothing wrong with the execution plans you've provided. The SQL Engine is doing its job as expected. Different operations are more efficient and faster depending on the size ...
J.D.'s user avatar
  • 37.9k
2 votes

Does PERCENTILE_CONT still give terrible performance when calculating medians in 2022 versions of SQL Server?

Is PERCENTILE_CONT still extremely slow at this task in recent (i.e. 2022 or later) versions of SQL Server? Yes, it is. None of the percentile facilities are specifically optimized for the median ...
Paul White's user avatar
  • 85.8k

Only top scored, non community-wiki answers of a minimum length are eligible