Postgres SELECT DISTINCT Does Not Scale
This post dives deep into a surprising Postgres performance pitfall, revealing that SELECT DISTINCT doesn't scale as intuitively expected due to its underlying query plan. It meticulously explains why this common operation performs a full index scan and then offers a clever, if complex, recursive CTE workaround. For database enthusiasts and developers building scalable systems, this is a must-read for understanding and mitigating subtle performance bottlenecks.
The Lowdown
Many assume SELECT DISTINCT in Postgres scales efficiently with the number of unique values, especially with proper indexing. However, this article uncovers a significant scaling issue: SELECT DISTINCT consistently performs a full index scan, making its performance proportional to the total number of rows matching predicates, not just the unique results.
- The authors encountered this issue in a partitioned queue workload where
SELECT DISTINCT, intended to find unique active partitions, became prohibitively slow for 'narrow but deep' queues with many workflows per partition. - Their expectation was O(number of active partitions), but observed performance was O(total number of enqueued workflows), validated by benchmarks showing linear latency increase with rows per partition.
- Analyzing the query plan revealed Postgres performs a full index scan, iterating through every matching row to find distinct values, rather than an optimized 'loose index scan' like MySQL offers.
- Postgres lacks a 'loose index scan' operator, and while efforts like 'skip scan' exist, they don't solve this specific
SELECT DISTINCTproblem. - The solution involves a more complex workaround: a recursive Common Table Expression (CTE) that iteratively finds the minimum partition key, effectively simulating a loose index scan. This approach achieves the desired O(number of partitions) scaling.
- Benchmarks confirmed the recursive CTE's performance remained constant regardless of the number of rows per partition.
This deep dive serves as a crucial reminder that database behavior can be counter-intuitive and that understanding query planners is vital for building truly scalable applications. The authors, from DBOS, conclude by inviting interested developers to explore their work on Postgres-backed durable execution.