Press "Enter" to skip to content

Category: Query Tuning

PostgreSQL and Covering Indexes

Published 2025-06-30 by Kevin Feasel

Kendra Little does a bit of index management:

Dear Postgres, Why won’t you use my covering index?

Lately I’ve been learning to tune queries running against PostgreSQL, and it’s pretty delightful. One fun question that I worked through struck me something that most Postgres users probably encounter one way or another: sometimes you may create the perfect index that covers a given query, but the query planner will choose to ignore it and scan the base table.

Why in the world would it do that?

Read on to learn why.

Leave a Comment

PostgreSQL Query Tuning for the Oracle DBA

Published 2025-06-25 by Kevin Feasel

Kellyn Gorman continues a series on PostgreSQL for Oracle DBAs:

As an Oracle DBA venturing into the world of PostgreSQL, one of the most important areas to get comfortable with is performance tuning. While Oracle’s Cost-Based Optimizer (CBO) is a well-known powerhouse that many DBAs have learned to both respect and wrestle with, PostgreSQL offers its own sophisticated query planner that behaves differently.  Understanding these differences is key to becoming proficient with PostgreSQL as we step into tuning.

Read on to gain an overview of what options are available on the PostgreSQL side and how they differ from what’s available in Oracle.

Leave a Comment

Troubleshooting with Extended Events

Published 2025-06-11 by Kevin Feasel

Grant Fritchey knows one way to solve the problem:

A client asked us to tell them when a query ran long. Simple. We have a long running query alert, all built in to Redgate Monitor, so, done. No, see, we like getting alerted when queries run long, but not really long, plus we’re more concerned with just one database.

Click through for the story and how Grant was able to help out the client. Also, read the comments for an entry by Special Guest Star Erik Darling.

Comments closed

Combining DISTINCT and UNION

Published 2025-05-27 by Kevin Feasel

Louis Davidson gives it the college try:

When I was perusing my LinkedIn feed the other day, I came across this thread about using SELECT *. In one of the replies, Aaron Cutshall noted that: “Another real performance killer is SELECT DISTINCT especially when combined with UNION. I have a whole list of commonly used hidden performance killers!”

To which started my brain thinking… What does happen when you use these together? And when you use UNION on a set with non-distinct rows, what happens. So for the next few hours I started writing.

Read on for Louis’s findings.

Comments closed

Optional Parameter Plan Optimization in SQL Server 2025

Published 2025-05-23 by Kevin Feasel

Brent Ozar is down with OPP(O):

SQL Server 2025 improved PSPO to handle multiple predicates that might have parameter sensitivity, and that’s great! I love it when Microsoft ships a v1 feature, and then gradually iterates over to make it better. Adaptive Memory Grants were a similar investment that got improved over time, and today they’re fantastic.

SQL Server 2025 introduces another feature to mitigate parameter sniffing problems: Optional Parameter Plan Optimization (OPPO). It ain’t perfect today – in fact, it’s pretty doggone limited, like PSPO was when it first shipped, but I have hopes that SQL Server vNext will make it actually usable. Let’s discuss what we’ve got today first.

Okay, I really had to stretch the truth to make my lead-in work, but I’m too proud of it to change anything. Click through to see where OPPO is today. Even with just one optional parameter working well, there is still a class of stored procedures that this can help: the “get by one ID, or get me all of them” type.

Comments closed

Digging into Non-Idempotent Subqueries via CTE in PostgreSQL

Published 2025-05-05 by Kevin Feasel

Shayon Mukherjee continues pulling on a thread:

A few days ago, I wrote about a surprising planner behavior with CTEs, DELETE, and LIMIT in PostgreSQL, a piece I hastily put together on a bus ride. That post clearly only scratched the surface of a deeper issue that I’ve since spent way too many hours exploring. So here are some more formed thoughts and findings.

Click through for a deeper dive into the topic, including some key takeaways.

Comments closed

A Performance Comparison of Temp Tables and Table Variables

Published 2025-04-28 by Kevin Feasel

Mehdi Ghapanvari compares two ways of holding temporary data:

Do you ever wonder why your query is slow when you use a table variable in a join operation? If you are interested in why this happens and how to improve query performance in such a scenario, keep reading to learn more.

Click through for the full article. This focuses entirely on classic table variables and not memory-optimized table variables, the latter of which can significantly improve performance in specific circumstances.

Comments closed

Reviewing Two Explain Plans in PostgreSQL

Published 2025-04-17 by Kevin Feasel

Semab Tariq talks query tuning:

Performance optimization in a production database is crucial, but over-optimizing can complicate things without real improvements.

In this blog post, I’ll share two very basic EXPLAIN ANALYZE outputs from a production system. A user asked us to help optimize these plans (I’ve changed the table and column names for privacy).

We will look at how to spot slow parts of a query, improve performance the right way, and avoid unnecessary tuning.

One of the things you eventually learn as a performance tuner is that sometimes, it’s best not to try to optimize a particular query. This may seem a bit contradictory–who doesn’t want to go faster? But there are costs to actions, and spending a long time tuning an ad hoc query that somebody ran one time and probably won’t run again isn’t worth it. Ultimately, know how to tune, but also when to tune and what will give you the biggest marginal benefit.

Comments closed

Comparing INSERT INTO and SELECT * INTO

Published 2025-04-17 by Kevin Feasel

Haripriya Naidu runs an experiment:

Instead of looking at which option is faster, you may want to look at which option is better suited for a given context. Let’s take a look at 2 common options to insert data and analyze them.

INSERT INTO TARGETTABLE
SELECT * FROM SOURCETABLE

OR

SELECT * INTO TARGETTABLE 
FROM SOURCETABLE

Click through for a comparison of the two, not just for which is faster but also the pros and cons of each approach.

Comments closed