Why SELECT DISTINCT doesn’t scale on Postgres — rewrite patterns that do

SELECT DISTINCT is the most polite lie in SQL. It says “give me unique rows” as if uniqueness were free. On Postgres it is almost never free. It is a Sort or a HashAggregate sitting after the planner has already built the row set you asked for — including every duplicate a sloppy join just invented.

I spent years on a query engine team watching people treat DISTINCT like a lint fix for “too many rows.” It is not a lint fix. It is a late filter on a problem you usually should have prevented earlier.

What Postgres actually does

For a plain SELECT DISTINCT col1, col2, … Postgres has two honest plans:

Sort + unique. Order the projected rows, then walk them and drop adjacent duplicates. Cost tracks sort memory, spill, and the width of the sort key. Fine when the input is already almost unique and fits in work_mem. Painful when the join below produced a hundred million near-identical lines.

HashAggregate. Build a hash table keyed on the DISTINCT columns and keep one survivor per key. Great until the distinct cardinality blows past memory and you spill, or until the hash table itself becomes the product you were trying to shrink.

Either way the shape is the same: produce, then dedupe. There is no magic “DISTINCT index” that makes an exploded join cheap. Indexes help when you can avoid producing the duplicates. They do not make a late DISTINCT free.

DISTINCT ON (…) is a related tool with sharper edges: keep the first row per key after an ordered scan. It is powerful when you truly want “latest per customer” and you control the ORDER BY. It is also easy to misuse as DISTINCT-with-hope — wrong order, wrong survivor, silent logic bug.

Why it blows up

Three failure modes show up over and over.

High cardinality of the DISTINCT key. You thought you were collapsing a few duplicates. You were collapsing millions of nearly unique keys. Hash table grows. Sort spills. The query that “used to be fine” on staging with 1% of production data dies at 10 a.m.

Late DISTINCT after a multiplying join. Classic: join orders to order_items to shipments without fixing the grain, then SELECT DISTINCT order_id, … because the report “should be one row per order.” You paid for the fan-out in CPU, memory, and network, then paid again to throw most of it away. DISTINCT did not fix the join. It hid the bug in the EXPLAIN as an Aggregate node that looks legitimate.

SELECT DISTINCT *** on wide rows. You unique on every column, including fat text and JSON. Sort/hash width explodes. Even when logical uniqueness is on two keys, you made Postgres unique on the entire projection.

Agents love this pattern. A coding model sees duplicate rows in a result, wraps DISTINCT, and ships. The unit test with ten orders is green. Production with a many-to-many join is not.

Rewrite patterns that actually help

1. Fix the join grain before you unique.

If you need one row per order, do not join to every line item and DISTINCT the order columns. Aggregate the children first, or use a subquery / lateral that returns the one child attribute you need:

SELECT o.id, o.placed_at, s.last_shipped_at
FROM orders o
LEFT JOIN LATERAL (
  SELECT MAX(shipped_at) AS last_shipped_at
  FROM shipments s
  WHERE s.order_id = o.id
) s ON true;

Or pre-aggregate:

SELECT o.id, o.placed_at, sh.last_shipped_at
FROM orders o
LEFT JOIN (
  SELECT order_id, MAX(shipped_at) AS last_shipped_at
  FROM shipments
  GROUP BY order_id
) sh ON sh.order_id = o.id;

Same answer as “join then DISTINCT,” usually cheaper, and the intent is readable in the plan.

2. Prefer EXISTS / IN (with care) when you only need presence.

“Orders that have at least one refund” is not a DISTINCT problem:

SELECT o.*
FROM orders o
WHERE EXISTS (
  SELECT 1 FROM refunds r WHERE r.order_id = o.id
);

EXISTS can stop at the first match. SELECT DISTINCT o.* FROM orders o JOIN refunds r … often cannot. Semi-joins are what you meant.

3. GROUP BY when you are already aggregating.

If every non-grouped column is an aggregate or functionally dependent, GROUP BY is the honest shape. Do not SELECT DISTINCT a half-aggregated projection. Postgres is better at optimizing grouping that matches the question than at guessing why you distincted a random column list.

4. DISTINCT ON — only when the survivor rule is real.

SELECT DISTINCT ON (customer_id)
  customer_id, id, created_at, status
FROM tickets
ORDER BY customer_id, created_at DESC;

That is “latest ticket per customer.” The ORDER BY must start with the DISTINCT ON keys. If you cannot write the order rule in one sentence, do not use DISTINCT ON. Use a window (ROW_NUMBER() … = 1) when you need clearer control or portable logic — still pay for the window, but the intent is harder to get wrong.

5. Dedupe upstream — at ingest, in the app, or in a staging table.

If the duplicates are dirty data (double-loaded events, retry spam), DISTINCT in every reader is a tax. Unique constraints, upserts, and a nightly consolidation job are cheaper than teaching every dashboard to DISTINCT the same mess. The query layer should not be your dedupe warehouse.

6. Project narrow keys first, then join back.

When you truly need a late unique, unique the keys, not the wide row:

WITH keys AS (
  SELECT DISTINCT order_id
  FROM huge_join_result  -- still try not to build this
)
SELECT o.*
FROM keys k
JOIN orders o ON o.id = k.order_id;

Narrow DISTINCT, fat columns only once. Still second-best to not building huge_join_result.

When DISTINCT is fine

I am not anti-DISTINCT. I am anti-DISTINCT-as-band-aid.

It is fine when:

• The input is already small or nearly unique (dimension-style lookups, small IN lists expanded and collapsed).
• You are uniquing a cheap projection you intentionally over-fetched (e.g. id lists from a few OR branches) and EXPLAIN shows a tiny HashAggregate.
• Cardinality of the DISTINCT key is known-low and stable, and you measured it — not guessed from staging.
• The alternative rewrite is uglier and the plan is already good.

It is not fine when the DISTINCT node is the top of a join tree that multiplied rows by 50×, or when an agent added it because the UI showed duplicates and nobody looked at rows in EXPLAIN ANALYZE.

What a context-aware agent should do instead

A useful AI DBA does not suggest SELECT DISTINCT as the first fix for “too many rows.”

It should:

1. Look at the plan and the join graph — which join increased row estimates? Is there a missing predicate or wrong grain?
2. Check stats and actuals — n_distinct on the key, hash/sort memory, spills. High-cardinality DISTINCT is a smell, not a style note.
3. Propose a rewrite — EXISTS, pre-aggregate, LATERAL, GROUP BY, or DISTINCT ON with an explicit survivor rule — and show the before/after shape.
4. Warn before the band-aid — if the only change is wrapping DISTINCT around a multiplying join, say so out loud: you are hiding fan-out, not fixing it.

That is the context layer job: schema + workload + the live plan, not a chat tip that DISTINCT “removes duplicates.” Soft product line, honest: at deepsql.ai we care more about catching the fan-out before an agent ships the DISTINCT wrap than about inventing a clever Aggregate node.

Honest gap, same as always: DeepSQL is not your query rewriter for every dialect edge case, and it will not magically make a billion-key DISTINCT cheap. It should make the bad suggestion harder to accept without looking at the plan.

The short version

SELECT DISTINCT on Postgres is a late Sort or HashAggregate. Use it when the stream is already almost unique. Do not use it to apologize for a join that multiplied rows. Fix grain with pre-aggregation, EXISTS, LATERAL, or DISTINCT ON with a real order rule. Dedupe dirty data upstream. And if an agent’s first idea is DISTINCT, ask it to show the join that made the duplicates — that is usually the real bug.

If you’ve got a DISTINCT that “fixed” a report and then melted in production, send the blunt EXPLAIN. Peer notes beat another DISTINCT tutorial.

---

Venkat Sakamuri · Founder, DeepSQL · Ex Oracle Core Query Engine · Peer DBA/eng building a self-hosted AI DBA for Postgres & MySQL · venkat@deepsql.ai