Postgres SELECT DISTINCT Does Not Scale

summary

PostgreSQL evaluates SELECT DISTINCT by performing a full index scan of all matching rows rather than jumping directly to unique values, causing performance to scale with table size rather than the number of unique entries. When testing a queue workload with few partitions but many rows, the authors found query latency grew linearly into seconds. To bypass this limitation, they implemented a recursive common table expression that mimics a loose index scan by iteratively fetching the minimum value. Commenters note that this behavior is a longstanding limitation without a native loose index scan operator.

key points

1. SELECT DISTINCT in PostgreSQL always scans every row matching the predicate, ignoring the uniqueness of the target column in index evaluations.
2. Query execution time scales linearly with the number of rows per partition rather than the number of distinct values returned.
3. A recursive common table expression can force PostgreSQL to execute a loop over minimum values, bypassing full index scans for O(number of partitions) performance.

who should read it

Backend engineers and database administrators optimizing PostgreSQL query performance under high-volume queue workloads.

discussion skeptical