Postgres treats SELECT DISTINCT as a full scan problem regardless of how many unique values actually exist in your table. Even if you maintain a perfect B-tree index and only need ten distinct keys out of ten million rows, the query engine will dutifully read every single matching entry.
The root cause comes down to the lack of an index skip scan feature in Postgres. While databases like Oracle and MySQL can jump directly to the next distinct key within a B-tree, Postgres scans linearly through duplicate values before discarding them. For partitioned queue systems that poll for distinct active queues, this turns an intended low-latency operation into an IO bottleneck.
The standard production workaround is to emulate loose index scans using recursive common table expressions. A recursive query looks up the minimum key, jumps to the next value strictly greater than the current key via an index seek, and stops when no values remain.
Benchmarking this recursive approach against a raw SELECT DISTINCT routinely drops query durations from seconds down to sub-millisecond ranges.
A simple schema design cannot save you when query execution plans rely on full scans by default.









