Unique
Appears in EXPLAIN asUnique
Removes adjacent duplicate rows from sorted input.
What it is
A Unique node eliminates duplicates by comparing each row to the previous one — which only works on sorted input. It implements DISTINCT and UNION's deduplication when a sort-based plan is chosen.
When the planner picks it
For DISTINCT / UNION when the input is (or is cheaply made) sorted; the alternative is a HashAggregate.
Is it good or bad?
Cheap on top of already-sorted input. If a big Sort exists only to feed Unique, a HashAggregate may be the better DISTINCT strategy.
In depth
Adjacent duplicates only
A Unique node strips duplicates by comparing each row to the previous one —
which only works if the input is already sorted on the columns being
deduplicated. It implements SELECT DISTINCT and the deduplication step of
UNION (without ALL) when the planner chooses a sort-based approach.
Because it only ever holds the previous row, Unique itself is essentially free. The cost is whatever feeds it sorted input.
When sort beats hash for DISTINCT
SELECT DISTINCT a, b FROM t has two main strategies:
- Sort + Unique — sort by
(a, b)and pass through the dedupe. - HashAggregate — build a hash table of distinct keys.
The planner picks based on estimated cost. Sort + Unique wins when an index
already provides the order (no sort needed) or when the input is small enough
that the Sort is cheap. HashAggregate wins on big, unordered inputs that fit
in work_mem — and since PostgreSQL 13 it spills to disk gracefully if not.
When you see a Sort just for Unique
Unique
-> Sort (Sort Method: external merge Disk: 38000kB)
-> Seq Scan on big
A big external-merge Sort whose only purpose is to feed Unique is a strong
hint that a HashAggregate would be cheaper — check that enable_hashagg is
on and work_mem is realistic.
What the analyzer flags here
- Sort spilled to disk — a sort feeding DISTINCT exceeded work_mem