Query Tuning – Curated SQL

An Overview of pg_plan_advice

Published 2026-04-22 by Kevin Feasel

Christophe Pettus continues a series on plan hints in Postgres:

Robert Haas’s pg_plan_advice patch set, proposed for PostgreSQL 19, is where the twenty-year argument from Part 2 has landed — or is trying to. It is not pg_hint_plan brought into core. It is a different thing, with different mechanics, a different scope, and a different answer to the “why is this different from Oracle-style hints” question.

Read on to learn more about the proposal and how this resolves some of the core issues that led the major Postgres maintainers to reject query hints for so long.

Query Hints and Plan Guides in RDBMS Products

Published 2026-04-21 by Kevin Feasel

Cristophe Pettus has a series in progress. The first post covers the basics of query hints and plan guides:

pg_plan_advice is expected to land in PostgreSQL 19. That makes this a good moment to look at query hints — what they are, what every other major database does with them, and how PostgreSQL ended up being the obvious outlier. Three parts. This is the first.

The second post explains why PostgreSQL hasn’t had query hints:

For most of PostgreSQL’s history, the official community position on query hints has been a polite version of “no, and stop asking.”

The position isn’t subtle. The PostgreSQL wiki maintains a page titled Not Worth Doing, and “Oracle-style optimizer hints” is listed there, right above in-process embedded mode and obfuscated function source. The companion wiki page, OptimizerHintsDiscussion, states the position outright:

Click through for a bit of history and comparison. The upcoming post promises to go into pg_plan_advice‘s proposal in more detail.

Dealing with Bad Parameter Sniffing

Published 2026-04-14 by Kevin Feasel

Hugo Kornelis has a new video:

The video starts with an explanation of two good features: parameter sniffing and plan caching. But those features can interact in an unwanted way, resulting in erratic bad performance. Now we have what I call “bad parameter sniffing”.

Starting at approximately 8:30, I then describe the three most common root causes for bad parameter sniffing: equality filters on a column with a skewed data distribution; inequality filters with varying selectivity; and optional parameters.

Click through for the rest of the synopsis, as well as the video itself.

Binary Search for Chronological Records in SQL Server

Published 2026-03-18 by Kevin Feasel

Andy Brownsword performs several probes:

Specifically we’ll use a binary search approach to narrow the search range. We abuse the correlation between the clustering key and timestamp to zero in on the records, using the key for navigation, and the timestamp to guide us.

We’ll start with the first and last records as boundaries, followed by checking the timestamp at the mid-point. Depending on whether the timestamp is before or after our target point in time, the appropriate boundary is moved. This halves the key space, and the search repeats until we’ve narrowed the range sufficiently to scan a very small portion of records.

It’s a neat idea, though do watch for Andy’s warning at the end.

Comments closed

Making Row-Level Security Faster

Published 2026-03-12 by Kevin Feasel

Brent Ozar speeds up some operations:

The official Azure SQL Dev’s Corner blog recently wrote about how to enable soft deletes in Azure SQL using row-level security, and it’s a nice, clean, short tutorial. I like posts like that because the feature is pretty cool and accomplishes a real business goal. It’s always tough deciding where to draw the line on how much to include in a blog post, so I forgive them for not including one vital caveat with this feature.

Click through for that caveat, as well as how you can mitigate its performance impact.

Comments closed

Testing Implicit Conversion and Performance in SQL Server

Published 2026-03-12 by Kevin Feasel

Louis Davidson runs some tests:

If you have ever done any performance tuning of queries in SQL Server, no doubt one of the first thing you have heard is that your search argument data types need to match the columns that you are querying. Not one thing in this blog is going to dispute that. Again, the BEST case is that if your column is an nvarchar, your search string matches that column datatype. But why is this? I will do my best to make this pretty clear, especially why it doesn’t always matter.

Read on as Louis lays out the explanation.

Comments closed

Performance Studio

Published 2026-03-06 by Kevin Feasel

Erik Darling has a new free tool:

Stop clicking through SSMS execution plans like it’s 2005.
Performance Studio is a free, open-source plan analyzer that tells you what’s wrong,
where it’s wrong, and how bad it is — from the command line, a desktop GUI,
an SSMS extension, or an AI assistant.

Built by someone who has stared at more execution plans than any reasonable person should.

Click through for some of its capabilities, as well as how to get your hands on a copy.

Comments closed

Query Tuning and Premature Optimization

Published 2026-03-04 by Kevin Feasel

Denny Cherry shares some advice:

This runs about as inconsistently as you would expect, given that it’s the same plan every time, no matter what values are being passed in. Getting this to perform better and consistanly requires some dynamic SQL changes that look similar to the following.

Denny’s scenario is a very common one: as developers, we don’t know which access paths users will take, so we try to develop generic solutions that can cover a wide variety of scenarios. In practice, users land on a certain set of access patterns, and now we have actual queries we can ensure work as well as possible. Except for the parts where we painted ourselves into a corner with the original generic design. But hey, that’s what the imagined rebuild that will never happen can solve.

Comments closed

JSONB Data in Postgres and Performance Due to TOAST

Published 2026-02-27 by Kevin Feasel

Paul Ramsey lays out the facts and the data:

Working with APIs and arrays in the jsonb type has become increasingly popular recently, and storing pieces of application data using jsonb has become a common design pattern.

But why shred a JSON object into rows and columns and then rehydrate it later to send it back to the client?

The answer is efficiency. Postgres is most efficient when working with rows and columns, and hiding data structure inside JSON makes it difficult for the engine to go as fast as it might.

Read on to learn how Postgres manages to store arbitrary-sized JSONB data within the limitations of 8KB pages, and the performance implications of doing so.

Comments closed

Performance Tuning Dependent SQL Queries in DirectQuery Mode

Published 2026-02-24 by Kevin Feasel

Chris Webb tries a change:

As I described here, Power BI can send SQL queries in parallel in DirectQuery mode and you can see from the Timeline column there is some parallelism happening here – the last two SQL queries generated by the DAX query run at the same time – but everything has to wait for that first SQL query to complete. Why? Can this be tuned?

Click through for an example. I was thinking about how challenging it would be to improve this performance at the SQL query level and if you could build a single query that operates over all three sets of data—distinct customers, distinct customers on Mondays, distinct customers in Januaries–while still performing acceptably. I’m not sure that the variants I sketched out in my head would actually perform faster, thanks to the “distinct” requirements.

Comments closed

Category: Query Tuning