Category: Query Tuning

Jamal Hansen has a primer on explain plans for Python developers:

We talked early in this series about SQL being a declarative language. You tell the database what you want, and it figures out how to get it. But we’ve also seen that SQL gives you the freedom to do things in many ways, and some of those ways are more efficient than others.

Sometimes, a slow query means you didn’t choose the most efficient approach. Other times, your data has simply outgrown the default way the database finds records, and you need to give it a little help.

And in a judo move, if you already understand how explain plans work, you can figure out how to perform code profiling in Python.

Leave a Comment

Erik Darling learns us some T-SQL:

But we can see that the number of page splits that have occurred on the server have gone up a bit here. We can see that that number has increased. So if we look back at the table itself now, and we look in here, right, we’re still not going to have any forwarded fetches because that’s never a thing.

But we do have a lot more pages in the table now, and the average page space used in percent has gone down dramatically. This was at 99-something percent. We are now below 50%.

We are at 46% full. Well, that doesn’t feel too good. What this means is that SQL Server has a lot more pages in the table now that are a whole lot less full, which means we are sort of like yesterday when we deleted a bunch of data from the heap and we still read empty pages.

Click through for the video.

Leave a Comment

Eduardo Pivaral searches for expensive queries:

Slow-running queries can degrade your Redshift cluster’s performance and lead to increased costs. Identifying the most expensive queries is crucial to optimize resource usage and improve overall system efficiency.

My immediate answer was “all of them,” though in fairness, I’ve primarily needed to deal with situations in which people set up Redshift without using Kimball-style modeling and queried with (essentially) SELECT *.

Leave a Comment

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.

Comments closed

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.

Comments closed

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.

Comments closed