When PostgreSQL’s Default Statistics Fall Short

The PostgreSQL query planner is generally reliable for both OLTP and analytical workloads, but it is not immune to miscalculations. A common weak point is cross-column correlation. By default, the planner collects statistics per column—distinct counts, data distribution, and similar metrics—but it has no inherent knowledge of how values in different columns relate. This becomes a problem when query results depend on that relationship.

Consider practical cases: age correlates with height, and a person’s country typically correlates with their primary language. The planner, however, treats such columns as statistically independent. For correlated data, this assumption leads to inaccurate row estimates and potentially poor execution plans.

Extended statistics, created via CREATE STATISTICS, address exactly this gap. Let’s walk through a concrete example.

A Perfectly Correlated Dataset

First, we generate a dataset with 10 million rows where a clear correlation exists between two columns:

1

2

3

4

5

6

7

8

9

10

test=# CREATE TABLE t_test (id serial, x int, y int, z int);

CREATE TABLE

test=# INSERT INTO t_test (x, y, z)

          SELECT id % 10000,

                 (id % 10000) + 50000,

                 random() * 100

          FROM generate_series(1, 10000000) AS id;

INSERT 0 10000000

test=# ANALYZE ;

ANALYZE

The key detail here is that y is exactly x plus 50,000. Requesting all distinct combinations of x and y should therefore yield just 10,000 rows—not the product of their individual distinct counts.

The Planner’s Independent Assumption

Now, let’s see how the planner estimates the number of groups for a query that groups by both x and y. To keep the output clear, we disable parallel query execution:

1

2

3

4

5

6

7

8

9

10

test=# SET max_parallel_workers_per_gather TO 0;

SET

test=# explain SELECT x, y, count(*) FROM t_test GROUP BY 1, 2;

                            QUERY PLAN

------------------------------------------------------------------------

HashAggregate (cost=741567.03..829693.58 rows=1000018 width=16)

     Group Key: x, y

     Planned Partitions: 32

     -> Seq Scan on t_test (cost=0.00..154056.75 rows=10000175 width=8)

(4 rows)

The planner predicts roughly one million groups. The reasoning is straightforward: it assumes the result is the product of the distinct values in each column—10,000 multiplied by 10,000. The actual execution shows this is far from reality:

1

2

3

4

5

6

7

8

9

10

11

12

test=# explain analyze SELECT x, y, count(*) FROM t_test GROUP BY 1, 2;

                                 QUERY PLAN

----------------------------------------------------------------------------------

HashAggregate (cost=741567.03..829693.58 rows=1000018 width=16)

              (actual time=2952.991..2954.460 rows=10000 loops=1)

     Group Key: x, y

     Planned Partitions: 32 Batches: 1 Memory Usage: 2577kB

     -> Seq Scan on t_test (cost=0.00..154056.75 rows=10000175 width=8)

                           (actual time=0.036..947.466 rows=10000000 loops=1)

Planning Time: 0.081 ms

Execution Time: 2955.077 ms

(6 rows)

In practice, only 10,000 groups exist because of the direct one-to-one correlation. The planner’s multiplication logic, driven by per-column distinct counts, produces a result that’s 100 times too large. Such overestimates can lead to inefficient memory allocation, poor join ordering, or problematic sort strategies—real costs in a data warehouse environment.

Adding Extended Statistics

The fix is declarative: create extended statistics on the correlated columns, specifying the kind of information you need. For grouping queries, that’s the ndistinct statistic:

1

2

3

4

test=# CREATE STATISTICS mygrp (ndistinct) ON x, y FROM t_test;

CREATE STATISTICS

test=# ANALYZE t_test;

ANALYZE

PostgreSQL will maintain this extended statistic automatically. A standard ANALYZE refreshes it as data changes. Re-running the same grouping query now shows an estimate that is essentially on the nose:

1

2

3

4

5

6

7

test=# explain SELECT x, y, count(*) FROM t_test GROUP BY 1, 2;

                            QUERY PLAN

-----------------------------------------------------------------------

HashAggregate (cost=229052.55..229152.39 rows=9984 width=16)

     Group Key: x, y

     -> Seq Scan on t_test (cost=0.00..154053.60 rows=9999860 width=8)

(3 rows)

With the estimate at 9,984 groups, the planner has the ground truth it needs to build a sensible query plan.

Beyond Distinct Counts

The ndistinct approach specifically targets GROUP BY accuracy. If your performance bottlenecks come from WHERE clauses that involve multiple correlated columns, you’ll want to explore the dependencies statistic type. It improves selectivity estimates for those conditions, giving you comparable gains for filter-heavy queries. Choosing the right statistic type—or combining them—depends on the shape of your slow queries.