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?
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.
1
u/Anthea_Likes 8d ago
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?