Press "Enter" to skip to content

Category: Query Tuning

Reading an EXPLAIN Plan in PostgreSQL

Published 2025-11-14 by Kevin Feasel

Andrea Gnemmi reads the plan:

A typical task DBAs and Developers perform is optimizing query performance. The first step, after identifying troublesome queries using a tool like the pg_stat_statements view, is to look at the execution plan to determine what is happening and how to improve.

In PostgreSQL this can be done using EXPLAIN or using third-party tools which use the same process to gather query execution data.

Click through to see what an explain plan looks like in PostgreSQL and ways to visualize those plans.

Leave a Comment

Common ORM Tuning Tips

Published 2025-10-28 by Kevin Feasel

Amy Abel shares some advice:

Recently, I thought a database query in a plan was straightforward. It looked innocent until I noticed strange behavior. The deeper I dug, the more I realized many people might be running into the same issue with ORM queries.

Click through for a toy version of the scenario, as well as two common problems with ORM tuning: blind index acceptance (which, admittedly, is a problem with or without ORMs) and implicit conversion on filters.

Leave a Comment

PostGIS Tuning via pg_stat_statements

Published 2025-10-24 by Kevin Feasel

Paul Ramsey wants to make spatial querying faster:

A reasonable question to ask, if you are managing a system with variable performance is: “what queries on my system are running slowly?”

Fortunately, PostgreSQL includes an extension called “pg_stat_statements” that tracks query performance over time and maintains a list of high cost queries.

This particular post is more about pg_stat_statements in general rather than specific advice for PostGIS, but it does lay out some recommendations for using pg_stat_statements in spatial-heavy environments.

Comments closed

ROW_NUMBER() Filtering Performance

Published 2025-10-22 by Kevin Feasel

Erik Darling answers an office hours question in detail. The question comes down to why a filter on ROW_NUMBER() where the row number is equal to 1 could differ from the same query where row number is less than or equal to 1. Knowing that ROW_NUMBER() starts at 1 and can never be anything other than a natural number, you’d think that SQL Server would treat these exactly the same. But Erik shows an example where the two can differ, and the answer was a good one. I will admit that my pre-video guess was wrong but once he showed the execution plans, things clicked. And, like Erik mentions, this is why it’s so important to dig into the execution plan, because the answers are typically in there somewhere.

1 Comment

Performance of User-Defined Functions in Fabric Warehouses

Published 2025-10-03 by Kevin Feasel

Jared Westover shares some findings:

In Part One, we saw that simple scalar user-defined functions (UDFs) perform as well as inline code in a Fabric warehouse. But with a more complex UDF, does performance change? If it drops, is the code-reuse convenience worth the price?

I’m surprised that the performance profile was so good. I had assumed it would perform like T-SQL user-defined functions—namely, worse in general.

Comments closed

Skewed Data and Skewed Parallelism

Published 2025-09-23 by Kevin Feasel

Erik Darling has a new slide deck. This video focuses on how skewed data can potentially lead to parallelism not distributing the data on different CPU threads effectively. Erik demonstrates how you can identify this issue in practice, though solving the problem will come in a later video. It’s an interesting scenario and stick around for a poke at an argument Pedro Lopes has made.

Comments closed

Performing Data Validation with RegEx in SQL Server 2025

Published 2025-09-15 by Kevin Feasel

Reitse Eskens tries out regular expression support in SQL Server 2025:

One of the new features in SQL Server 2025 is that you can now use regular expressions directly in your T-SQL queries. Now, regular expressions (or RegEx) have never been a syntax that’s easy to read. There are a lot of brackets, dashes and other symbols that make no sense when you first see them. Before delving into how these can be used in SQL Server, a few basics are provided to get you started, along with a link to a website for further learning.

Read on for a quick primer and a bit of pain when it comes to performance.

Comments closed

Join Operator Properties and Query Performance

Published 2025-08-26 by Kevin Feasel

Andy Brownsword takes a closer look at the big three join operators in SQL Server:

In the previous post I explained Join Operators in SQL Server. Whilst compiling that I dug a little deeper and came across a few interesting points I thought were worth sharing.

Let’s look at behaviour of the operators which may occur under specific conditions. Hopefully you find them as interesting as I did:

Click through for an interesting tidbit about nested loops joins, merge joins, and hash joins.

Comments closed