Postgres SELECT DISTINCT Does Not Scale
2 days ago
- SELECT DISTINCT in Postgres always scans all rows matching its predicates, not just the unique values.
- This results in performance scaling with total rows instead of number of distinct values, causing slow queries for 'narrow but deep' workloads.
- Postgres lacks a loose index scan operator that would efficiently skip duplicate values, unlike MySQL.
- A workaround using a recursive CTE with sequential min() queries achieves O(number of unique values) performance.