Press "Enter" to skip to content

Category: Query Tuning

Query Plan Hints in Postgres 19

Published 2026-10-01 by Kevin Feasel

Elizabeth Garrett Christensen says you may not need it:

For years the Postgres community was against adding support for hints. They felt if you had correctly analyzed tables, the Postgres query planner would make the right choice. And they’re not wrong, for the most part. Yet many DBAs and developers will tell you of a time when a plan suddenly changed or something was saved by a hint. We can debate the good and bad elements of hints, but the cool thing is that you will soon have a choice in Postgres 19+.

Read on to see how plan hints will work and what you should try before using them.

Leave a Comment

Window Functions and Virtual Tables

Published 2026-09-29 by Kevin Feasel

Dualcore DBA pushes a predicate:

In this post, we’ll look at some interesting behaviour I’ve stumbled across with window functions and derived datasets in SQL Server.

I came across a query in a workload that was looking to get the most recent thing per group, a representative example in the Stack Overflow 2010 Database is below. The database is provided under cc-by-sa 4.0 licence from Stack Exchange Data Dump, I am running in compatibility level 150 on a SQL Server 2022 instance installed on a VM with 8 cores and 35GB RAM, though the issue being illustrated also occurs in earlier compatibility levels.

Click through for the demonstration and to see what happens when you switch to compatibility level 160.

Leave a Comment

Fetching Query Plans in Fabric Data Warehouse

Published 2026-09-24 by Kevin Feasel

Louis Davidson shows a process:

So in this next entry in this series, I want to start to cover a topic that every nerdy SQL programmer is going to want to know. “How do I make a query faster?” If you have worked with relational databases, you know the simple answer is to… check the estimated query plan, run the query and look at the actual plan, and in many cases to be completely honest, add indexes.

Click through to see how closely your current SQL Server skills translate in this instance. The specifics of query tuning and table design in the Fabric Data Warehouse will differ a bit from on-premises SQL Server, but at this level, it’s quite similar.

Leave a Comment

The Pain of Time Zones

Published 2026-09-14 by Kevin Feasel

Erik Darling makes an admission:

Erik Darling here with Darling Data, and in this video, we’re going to talk about mostly AT TIME ZONE performance, but also some other interesting stuff about AT TIME ZONE. I’m going to be real honest with you, I hate timezone stuff. I hate all of it. I’ve never taken naturally to it. It’s all too weird, and managing it is just such a nightmare. 

I agree with Erik’s point about storing everything in UTC. Click through to learn how you can slow things down considerably using AT TIME ZONE in certain ways.

Leave a Comment

Cross-Table Date Math

Published 2026-09-04 by Kevin Feasel

Erik Darling does some date math covering multiple tables:

So, so we’re going to use this query, which, if I remember its provenance correctly, came from the Stack Data Explorer site. I can just never find it when I go look there again. But it’s, it’s, it’s, it’s the intent of the query is to find posts that had a lot of very early upvotes, and this query was always very slow, and to me, the interesting part of the query was that the where clause was looking for a date diff in columns on two tables. Now, under normal circumstances, if you had both of these columns in the same table, right, you could, you could, if you were denormalized a bit, but this would be a terrible denormalization.

Click through for a clever use of an indexed view and a non-clustered columnstore index. Which, incidentally, marks one of the few times in which I’ve seen actual value in non-clustered columnstore indexes.

Comments closed

The Pain of Residual Predicates

Published 2026-08-28 by Kevin Feasel

Brent Ozar has a new animation:

If we modify our query a little by selecting all of the columns instead of just Id and Location, then we have to do a Key Lookup, like we talked about in the How to Think Like the Engine class. For each person who lives in Helsinki, we have to look up their row in the clustered index in order to fetch all the columns we need. That’s not really a big deal, though, as long as a relatively limited number of people live in Helsinki. Like I wrote in that post, the index seek + key lookup is essentially two index seeks: one into Helsinki, and then one seek (for each Helsinki resident) on the clustered index, by their Id.

However, let’s add a little more complexity to the query:

Click through for a scenario in which a more selective query can result in worse performance than a less-selective variant.

Comments closed

sp_executesql and Building Execution Plans

Published 2026-08-19 by Kevin Feasel

Dualcore DBA needs a plan:

Whilst I would say the actual execution plan is the most useful, estimated execution plans have their place – sometimes you just need to see estimates or a quick verification that a change you have made has had some effect on plan shape. I find them helpful to quickly see if a change made to the code, an index, database setting or similar has had an effect on the execution plan without having to wait for the query to finish (especially if I am performance tuning a query that has a long run time).

sp_executesql is also useful – it helps us to execute dynamically created queries and supports parameterisation. It is also used by the .NET SqlCommand class to issue queries to the database engine in a parameterised form, assisting with SQL injection prevention along the way.

I recently found myself troubleshooting some code coming from an application. It was a Slow in the app, fast in SSMS problem so I was using sp_executesql to replicate the application behaviour. I also wanted to get the estimated execution plan. Here is an example query and what I was greeted with when I asked for the estimated plan:

Read on for one challenge you might find when trying to tune operations that you’ve built using sp_executesql, as well as what you can do about it.

Comments closed

Digging into Postgres Plan Caching

Published 2026-08-13 by Kevin Feasel

Jordan Boich runs an experiment:

In my last post, I talked about how I was amazed that PostgreSQL doesn’t have a shared plan cache like SQL Server does. I wanted to create an experiment / lab where I could see this in action, so I built one. I’m going to be leveraging a free PostgreSQL database hosted on an Azure Flexible Server. I’ll also be using DBeaver to connect to my PG Database.

Click through to see what Jordan learned.

Comments closed

From CTE to Temp Table

Published 2026-08-12 by Kevin Feasel

Brent Ozar describes a pattern I often use:

Common Table Expressions are awesome because they let SQL Server reorder processing in whatever way it deems to be the most efficient for your current data distribution, on your current version of SQL Server. Default to CTEs.

When SQL Server gets that process wrong, switch to temp tables.

Read on for an example.

Comments closed

Dealing with Catch-All Queries

Published 2026-08-11 by Kevin Feasel

Dualcore DBA handles an overly broad class of query:

We have a query that SELECTs from the Users table that takes multiple optional parameters and filters the output based on those.

Whilst fairly easy to read and write, queries written in this way often underperform.

This is a very generous understatement. Click through for two ways to improve the performance of such queries, as well as the pros and cons of each.

Comments closed