← Saturday, September 26, 2026DatabasesDeep dive

Postgres SELECT DISTINCT scans every matching row despite indexes

dbos.dev 5 min read Editor score 8/10

In a Postgres-backed queue workload, SELECT DISTINCT became the most expensive query because it scans every row matching the predicates, even when few unique values are needed. The explanation of the database's design trade-offs and workarounds can inform high-volume query design.

What

Postgres SELECT DISTINCT scans every row matching its predicates, even with an index and few distinct values to return.

How

In a partitioned-queue workload, the query slowed on deep partitions; latency grew linearly with rows per partition.

Why it matters

Engineers should check query plans rather than assume indexed deduplication scales with unique results; simple queue queries can dominate workload cost.

Read on dbos.dev → Hacker News · 28 comments

Headline, summary, brief and diagram are written by a model from the source's own text; the link, byline and numbers come from the source. Read the original before relying on a claim.