Press "Enter" to skip to content

Category: Statistics

Diagnosing High-Cardinality Workloads in Postgres

Published 2026-08-14 by Kevin Feasel

Ryan Booz has a video:

In Part 6 of this special Postgres in Production deep dive series, Ryan Booz asks a question that determines how useful pg_stat_statements can be for you at all: do you have a high cardinality workload? This episode covers what that actually means, why ORMs, dynamic SQL, and AI-assisted development tools generate more unique queries than you might expect, a side by side demo of the same workload on Postgres 17 and Postgres 18, and the concrete checks that tell you whether pg_stat_statements is losing the data you need for query tuning.

Click through for the video and transcript.

Leave a Comment

Viewing Statistics a SQL Server Query Used

Published 2026-08-04 by Kevin Feasel

Dualcore DBA takes a look:

Something I have been making use of recently is the change in the way statistics that have been used to compile a plan are presented to us as SQL Server users. On a number of occasions I have needed to understand, or at least get some sense of, which statistics SQL Server has used to come up with some of its estimates, usually when troubleshooting a poor estimate. This information has been available to us for a while, but up until fairly recent (OK – define recent, I guess!) versions of SQL Server, it was a bit hidden.

Granted, “fairly recent” means “all of the SQL Server versions that no longer have official support.” But there’s a compatibility level factor at play as well, so click through to learn more.

Comments closed

Tables without Histograms

Published 2026-06-29 by Kevin Feasel

Guy Glantser goes looking for stats:

A few weeks ago, I published a blog post titled “When Statistics Stay Empty Forever: A Hidden Edge Case with Empty Tables”

In that post, I described a scenario where statistics are created on an empty table, resulting in a statistics object without a histogram. If AUTO_UPDATE_STATISTICS is disabled and your maintenance solution doesn’t explicitly update statistics whose modification_counter is NULL, those statistics may never get a histogram – even after the table grows to millions or billions of rows.

Recently, I ran into another case involving missing histograms.

In this case, Guy found a table with 7 million rows and no histograms. Click through to see how that’s possible.

Comments closed

When Multi-Column Statistics (Mostly) Aren’t

Published 2026-04-08 by Kevin Feasel

Brent Ozar digs into multi-column statistics:

The short answer: in the real world, only the first column works. When SQL Server needs data about the second column, it builds its own stats on that column instead (assuming they don’t already exist), and uses those two statistics together – but they’re not really correlated.

For the longer answer, let’s take a large version of the Stack Overflow database, create a two-column index on the Users table, and then view the resulting statistics:

Click through for a dive into row estimation when you have multiple columns in a WHERE clause and how statistics come into play.

Comments closed

Porting Statistics in PostgreSQL

Published 2026-03-09 by Kevin Feasel

Radim Marek imports production statistics:

In the previous article we covered how the PostgreSQL planner reads pg_class and pg_statistic to estimate row counts, choose join strategies, and decide whether an index scan is worth it. The message was clear: when statistics are wrong, everything else goes with it.

But there was one thing we didn’t talk about. Statistics are specific to the database cluster that generated them. The primary way to populate them is `ANALYZE` which requires the actual data.

Click through to see how Postgres handles this. It’s quite similar to SQL Server’s DBCC CLONEDATABASE in practice, it seems.

Comments closed

Permanently Empty Statistics

Published 2026-03-05 by Kevin Feasel

Guy Glantser takes us through an edge case:

Many SQL Server DBAs rely on automated statistics maintenance solutions such as Ola Hallengren’s maintenance scripts. These scripts typically update statistics only when the modification counter exceeds a threshold.

But there is a corner case that can cause statistics to remain empty forever, and many DBAs are not aware of it.

Read on to see how you can end up with no statistics at all on a table.

Comments closed

Reducing Row Count Estimation Errors in PostgreSQL

Published 2026-02-09 by Kevin Feasel

Shinya Kato lays out four approaches:

PostgreSQL’s query planner relies on table statistics to estimate the number of rows (estimated rows) each operation will process, and then selects an optimal execution plan based on these estimates. When the estimated rows diverge significantly from the actual rows, the planner can choose a suboptimal plan, leading to severe query performance degradation.

This article walks through four approaches I used to reduce row count estimation errors, ordered from least to most invasive. Due to confidentiality constraints, I cannot share actual SQL or execution plans, so the focus is on the diagnostic thought process and the techniques applied.

Click through for those thought processes.

Comments closed

Automatic Stats Updates and Plan Cache Invalidation

Published 2026-01-30 by Kevin Feasel

Brent Ozar threads the needle:

Normally, when SQL Server updates statistics on an object, it invalidates the cached plans that rely on that statistic as well. That’s why you’ll see recompiles happen after stats updates: SQL Server knows the stats have changed, so it’s a good time to build new execution plans based on the changes in the data.

However, updates to system-created stats don’t necessarily cause plan recompiles.

Read on for the rare situation in which invalidation doesn’t happen.

Also, Brent has me wondering if the lemon popcorn is more sweet/tart (like a lemon bar) or savory (like lemon pepper) and I may have to try both styles.

Comments closed

Table Statistics and Planning Slowdowns

Published 2026-01-29 by Kevin Feasel

Andrei Lepikhov digs into a performance issue:

A query executes in just 2 milliseconds, yet its planning phase takes 500 ms. The database is reasonably sized, the query involves 9 tables, and the default_statistics_target is set to only 500. Where does this discrepancy come from?

This question was recently raised on the pgsql-performance mailing list, and the investigation revealed a somewhat surprising culprit: the column statistics stored in PostgreSQL’s pg_statistic table.

Read on for Andrei’s analysis and some interesting thoughts on possible avenues for improvement.

Comments closed

Statistics on Partitioned Tables in PostgreSQL

Published 2025-09-03 by Kevin Feasel

Laurenz Albe gathers stats:

I recently helped a customer with a slow query. Eventually, an ANALYZE on a partitioned table was enough to fix the problem. This came as a surprise for the customer, since autovacuum was enabled. So I decided to write an article on how PostgreSQL collects partitioned table statistics and how they affect PostgreSQL’s estimates.

Read on to see how it works and how you can generate statistics at the table level and not just the partition level.

Comments closed