Press "Enter" to skip to content

Category: Query Tuning

Execution Plan Analysis in Postgres with StatsViaExplainAnalyze

Published 2024-08-02 by Kevin Feasel

Deepak Mahto shares a tool with us:

Tuning SQL is an integral part of most developer’s or DBA’s primary responsibilities. Understanding and dissecting problematic execution plans are critical steps in the overall tuning process.

One key ingredient of SQL performance tuning, along with the runtime plan, is object statistics. Looking only at the execution plan is often insufficient for making solid recommendations; we need a fair understanding of current object statistics.As the saying goes,

Behind every optimized SQL query lies a deep understanding of the underlying object stats.

Click through to see examples of the StatsViaExplainAnalyze tool and how you can get a copy of it from GitHub.

Comments closed

The Performance Impact of Local Variables

Published 2024-08-01 by Kevin Feasel

Jared Westover talks performance:

Often, developers use local variables when writing ad hoc queries or stored procedures for many reasons. You might hear “never repeat code” or “avoid using magic numbers.” While writing a lengthy stored procedure, I might include a few. However, did you know that local variables can hurt the performance of your queries? How can you keep local variables from negatively affecting performance? Keep reading to find those answers and more.

This is the kind of performance issue that you can easily forget about. Jared includes two methods for resolving the issue if you run into performance problems on a specific query or stored procedure.

Comments closed

Statistics TIME and IO Analysis and Statistics Reporter

Published 2024-07-16 by Kevin Feasel

Rod Edwards does a bit of analysis:

Again, this is another one of those little bits of functionality that has been present since year dot. Unfortunately, it seems to be forgotten by many in the great game of query investigation.

Being able to see the IO patterns of a query that we’re running to help us see where the heavy lifting is occurring is really useful in allowing us to get the bottom of problems quickly, or see the benefits of our tuning efforts (or otherwise!).

One neat thing I did not know about before was a product called Statistics Reporter, an extension for SQL Server Management Studio that parses the results from time and I/O statistics results. I like Richie Rump’s Statistics Parser website, but the idea of having something built-in is pretty nice.

Comments closed

Keyset Pagination and Descending Order

Published 2024-07-16 by Kevin Feasel

Laurenz Albe digs into keyset pagination:

Keyset pagination is the most performant way to retrieve a large result set page by page. However, the neat trick with composite type comparison doesn’t always work. This article explains why and how you can work around that shortcoming.

All of the examples are for Postgres, though in my read-through of the post, I’d say it applies almost as well to SQL Server.

Comments closed

Merge Join vs Hash Join in Postgres

Published 2024-07-08 by Kevin Feasel

Andrei Lepikhov compares two physical join operators:

Today’s post is sparked by a puzzling observation: users, especially those who use an abstraction layer like REST or ORM library to interact with databases, frequently disable the MergeJoin option across the entire database instance. They justify this action by citing numerous instances of performance degradation.

Considering how many interesting execution paths MergeJoin adds to the system elaborating IncrementalSort or sort orderings derived from underlying IndexScan, it looks strange: one more bug of skewed cost balance inside the PostgreSQL cost model?

This is an interesting peek into how complex the query optimizers in database engines are, as well as how small amounts of information (via statistics or indexes) can matter to a query.

Comments closed

The Core of a Window Function’s Execution Plan

Published 2024-06-26 by Kevin Feasel

Grant Fritchey lays it out:

I showed the missing index suggestion (and let’s remember, they’re just suggestions) just for completion and to show I’m not hiding anything. Potentially, adding an index could speed up the query. However, that doesn’t affect what we’re going to talk about within this plan.

Logically, what’s happening here?

Click through for this overview, as well as a very helpful comment from Hugo Kornelis on what the Segment operator does.

Comments closed

Execution Plans for Keyset Cursors

Published 2024-06-25 by Kevin Feasel

Hugo Kornelis talks about a cursor I’d never heard of before:

Welcome to plansplaining, part 32, where we once more look at cursors. We already discussed the basics, and looked at static cursors and dynamic cursors. It is now time to cast our gaze upon the keyset cursor. The keyset cursor is sort of in between the static cursor (which presents a snapshot of the data is at was when the cursor was opened and disregards future changes) and the dynamic cursor (that always shows the current data). To be precise, a keyset cursor sees changes made to already existing rows, but does not see new rows that were added after the cursor was opened.

Read on to learn more about it.

Comments closed

Enumerations and Ordering in Postgres

Published 2024-06-24 by Kevin Feasel

Christoph Schiessl sorts things out:

Custom ENUM types in PostgreSQL are an excellent tool for enforcing certain database constraints, but you must be careful if you use SELECT queries and want to ORDER BY these columns. Recently, I had to fix a bug whose root cause was a misunderstanding of this behavior. It’s just a contrived example, but imagine a table of people with their marital status, which is implemented as a custom ENUM type.

Read on to learn more about the misunderstanding and some of the unexpected trickiness involved in getting a good query plan.

Comments closed

Using Query Store to Fix a Cardinality Estimation Problem

Published 2024-06-07 by Kevin Feasel

Michael Bourgon solves an issue:

This morning I had a performance issue on a piece of code that worked long ago on a different server, and they were trying to put it in place today.  It was SLOW. Like, 10 minutes slow. With the added bonus that it’s done through a web app, so it never finishes, it just always times out. After dealing with various approaches, I finally tried using the old Cardinality Estimator, and it went from 10 minutes to 3 seconds. But the query is inside the application, it doesn’t call a stored procedure. Which means the devs changing it is Non-Trivial. So I went to an updated version of an old trick – query store hints (which used to be Plan Guides)

Click through for a list of actions Michael took.

Comments closed

Dynamic Cursors in SQL Server

Published 2024-06-04 by Kevin Feasel

Hugo Kornelis continues a series on cursors:

We’re already at part 31 of the plansplaining series. And this is also the third part in my discussion of execution plans for cursors. After explaining the basics, and after diving into static cursors, it is now time to investigate dynamic cursors. As a quick reminder, recall that a static cursor presents data as it was when the cursor was opened (and does so by simply saving a snapshot of that data in tempdb), whereas a dynamic cursor is supposed to see all changes that are committed while the cursor is open. Let’s see how this change in semantics affects the execution plan.

Read on as Hugo gives it the college try and also admits he might be missing something in the explanation.

Comments closed