500 Milliseconds on Planning: How PostgreSQL Statistics Slowed Down a Query 20 Times Over

When legacy decisions meet current database realities

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.

The Context

In PostgreSQL, query optimisation relies on various statistical measures, such as MCV, histograms, distinct values, and others - all stored in the pg_statistic table. By default, these statistics are based on samples of up to 100 elements. For larger tables, however, we typically need significantly more samples to ensure reliable estimates. A thousand to 5000 elements might not seem like much when representing billions of rows, but this raises an important question: could large statistical arrays, particularly MCVs on variable-sized columns, seriously impact query planning performance, even if query execution itself is nearly instantaneous?

Investigating the Problem

We're examining a typical auto-generated 1C system query. '1C' is a typical object-relational mapping framework for accounting applications. PostgreSQL version is 17.5. Notably, the default_statistics_target value is set to only 500 elements, even below the recommended value for 1C systems (2500). The query contains 12 joins, but 9 are spread across subplans, and the join search space is limited by three JOINs, which is quite manageable. Looking at the EXPLAIN output, the planner touches only 5 buffer pages during planning - not much.

Interestingly, the alternative PostgreSQL fork (such forks have become increasingly popular these days) executed this query with nearly identical execution plans, and the planning time is considerably shorter - around 80 milliseconds. Let's use this as our control sample.

The Hunt for Root Cause

The first suspicion was obvious: perhaps the developers expanded the optimiser's search space, and it's simply passing through multiple extra paths. A flamegraph comparison between the slow planning case and the alternative fork showed remarkably similar patterns. Both exhibited search space expansion from features standard in 1C-related PostgreSQL forks (Joinsel and 'Append of IndexScans'), but nothing surprising beyond that.

However, the detailed analysis of the flamegraph revealed something more telling: a performance bottleneck in the byteaeq() comparison operation, triggered by the cost_index() function's cost estimation and toast_raw_datum_size() calls. The optimiser invokes this repeatedly while evaluating all possible index combinations across various expressions - not just those explicitly mentioned in the query, but also derived ones through 'equivalence classes' created by equality operations.

The query references just three columns: inforg10621::fld10622rref, inforg10621::fld15131rref, and inforg8199::fld8200_rrref. Yet these are involved in 20 different expressions, 15 of which are join clauses. When you factor in the number of indexes on these tables - eight between the two - it becomes clear that the number of possible combinations can explode. But how can we confirm this suspicion? How many times does the optimiser actually consult table statistics?

Unfortunately, standard PostgreSQL doesn't provide this information directly. So I turned to my own project - pg_index_stats, which uses PostgreSQL's internal hooks (relation_stats_hook and get_index_stats_hook) to collect precisely this data and display it in EXPLAIN output.

Here's what we found (1c and alternative):

The statistics for four columns are being accessed more than 100 times each. Remarkably, for the fld10622rref column, the optimiser fetches, decompresses, and uses the statistics 217 times! While this is less critical for the fld809 column (which has no histogram or MCV due to its nearly unique nature), other columns require repeatedly decompressing substantial arrays. The alternative fork accesses statistics roughly twice as frequently - a significant improvement, though not quite enough to fully explain the planning time difference.

Digging Deeper

What statistics do we actually have, and in what volume? Comparing statistics dumps from both PostgreSQL versions (here and there) shows that our tables indeed contain MCV and histogram arrays with up to 500 elements for several columns. Their uncompressed size reaches tens of kilobytes (compressed, over 2KB), and extracting them requires decompression before use. Surely we don't need to fetch and decompress these large arrays repeatedly?

After all, PostgreSQL does have caching that should calculate selectivity for a given expression only once …

We have two obvious suspects: columns fld10622rref and fld8201rref. Let's test our hypothesis by mechanically zeroing out their statistics and seeing what happens:

UPDATE pg_statistic
SET stanumbers1 = CASE WHEN stakind1 = 1 THEN NULL ELSE stanumbers1 END,
    Stavalues1 = CASE WHEN stakind1 = 1 THEN NULL ELSE stavalues1 END,
    Stakind1 = CASE WHEN stakind1 = 1 THEN 0 ELSE stakind1 END,
    Stanumbers2 = CASE WHEN stakind2 = 1 THEN NULL ELSE stanumbers2 END,
    Stavalues2 = CASE WHEN stakind2 = 1 THEN NULL ELSE stavalues2 END,
    Stakind2 = CASE WHEN stakind2 = 1 THEN 0 ELSE stakind2 END
WHERE (starelid = ‘_inforg10621’::regclass AND staattnum = (
    SELECT attnum FROM pg_attribute
    WHERE (attrelid = ‘inforg10621’::regclass AND attname = ‘fld10622rref’)))
OR (starelid = ‘_inforg8199’::regclass AND staattnum = (
    SELECT attnum FROM pg_attribute
    WHERE (attrelid = ‘_inforg8199’::regclass AND attname = ‘_fld8201rref’)));

The result? EXPLAIN now shows planning time at around 30ms:

Planning: Buffers: shared hit=5 Memory: used=4030kB allocated=4096kB
Planning Time: 31.347 ms
Execution Time: 0.237 ms

If we delete all statistics entirely with:

DELETE FROM pg_statistic;

We get the theoretical minimum planning time for this query:

Planning: Buffers: shared hit=5 Memory: used=3932kB allocated=4096kB
Planning Time: 18.477 ms
Execution Time: 0.421 ms

This aligns perfectly with the alternative fork's planning time.

But in the current master branch, since commit 057012b, Postgres employs a hashing technique to reduce the N^2 overhead of long MCV array passes. Ok, let's backpatch our case and check the explain:

 Planning:
   Buffers: shared hit=5
   Memory: used=3984kB  allocated=4096kB
 Planning Time: 64.603 ms
 Execution Time: 0.197 ms

It is definitely better than before, but we still see overhead that may grow with larger statistical arrays and repeated detoasting/decompression attempts.

The Verdict

Statistics indeed causes the excessive planning time, but the question remains: is it the overhead of decompressing statistics, or the overhead of repeatedly iterating through long MCV and histogram arrays? The answer is likely both.

We can indirectly confirm the impact of repeatedly traversing MCV arrays by noting that changing the storage type of columns in pg_statistic from EXTENDED to EXTERNAL produces no measurable difference:

DELETE FROM pg_statistic;
SET allow_system_table_mods = ‘on’;
ALTER TABLE pg_statistic ALTER COLUMN stavalues1 SET STORAGE EXTERNAL;
…
VACUUM ANALYZE;

Conclusion and Solutions

The root cause is clear: the optimiser's search space expanded due to increased index counts and statistics sizes - both entirely legitimate scenarios that can occur beyond ORM applications. The execution itself remains efficient and doesn't consume significant disk or memory resources, so it doesn't significantly impact neighbouring operations. However, the planning time can become problematic.

What Can Be Done?

First approach: Implement a caching system for frequently accessed, extensive statistics. This could even be implemented as an extension (similar to how I collected statistics access patterns in pg_index_stats). The code wouldn't be overly complex - just a standard module allocating a DSM segment for a hash table and decompressed statistics. Additionally, it's worth exploring a balance and perhaps storing MCVs in sorted order (when the data type allows), enabling fast element matching on both sides during JOIN estimation and quick lookup during filter estimation.

Second approach: You can just reduce the statistics size on problematic tables or columns:

ALTER TABLE table_name ALTER COLUMN column_name SET STATISTICS 0;

Of course, the challenge here is detecting the problematic spots (columns, clauses) inside the query. There's no universal answer - you need to EXPLAIN on suspicious queries with and without statistics, then perform the same analysis I did above. And naturally, report findings to the vendor, because there's always room for improvement!

THE END.
Istanbul, Turkey. January 26, 2026.