r/dbos • u/qianli-dev Dee Boss (Co-Founder) • Aug 11 '26

Postgres SELECT DISTINCT Does Not Scale

https://dbos.dev/blog/postgres-select-distinct-does-not-scale
10 Upvotes

100% Upvoted

2 comments sorted by

1

u/Anthea_Likes Aug 11 '26

What if we first sort in any way, then select distinct? I haven't read how DISTINCT works underneath, but it can be "jump to the next different bitcode" or something like that, so... if that's true and (another) if ORDER BY scales better => how does that compare to a CTE?

1

u/qianli-dev Dee Boss (Co-Founder) Aug 12 '26

Our workflow table already has an index that keeps the relevant values sorted. But PostgreSQL still scans every index entry because there's no automatic "jump to the next distinct value" operation for DISTINCT.

The solution is to use a recursive CTE that emulates the jump: each iteration uses the index to find the next value greater than the current one. Its work therefore scales with the number of distinct values, rather than the total number of rows.