DISTINCT vs GROUP BY Plan Differences #

SELECT DISTINCT a, b FROM t and SELECT a, b FROM t GROUP BY a, b return identical rows, and are frequently planned differently. The difference is not folklore — it comes from where each construct sits in the query pipeline, and it decides whether you get a hash table, a full sort, or a free ride on an existing index.

This page compares the plan shapes and gives the rewrite rules that matter. The operators involved are described in aggregate and grouping strategies.

Why the Planner Treats Them Differently #

GROUP BY is part of the grouping stage, evaluated before the target list is finalised. The planner therefore considers both aggregate strategies for it: a hashed one and a sorted one.

DISTINCT is applied after the target list is computed, as a de-duplication step over the projected rows. PostgreSQL can implement it with a HashAggregate too, but the path to that decision is narrower — DISTINCT over an expression, or in combination with ORDER BY, frequently collapses to a Sort plus Unique because the ordering must be produced anyway.

DISTINCT ON is a third case with no hashed form at all. It requires the input sorted by the DISTINCT ON expressions, then by the ORDER BY list, and picks the first row per key. That is always a sorted plan.

Where each de-duplication construct is applied A left to right pipeline: scan, join, grouping stage where GROUP BY is applied, target list projection, distinct stage where DISTINCT and DISTINCT ON are applied, then ordering and limit. Labels show which operators are available at each stage. scan join grouping GROUP BY projection de-duplication DISTINCT / DISTINCT ON HashAggregate or Sort → GroupAggregate HashAggregate (plain DISTINCT) Sort → Unique (always for ON) same result rows, different stage — and therefore a different set of legal operators

Annotated Evidence #

Start with the pair that is usually equivalent:

-- DISTINCT: hashed here, because the columns are plain and there is no ORDER BY
EXPLAIN (ANALYZE) SELECT DISTINCT customer_id, status FROM orders;

HashAggregate  (actual time=1402.6..1489.1 rows=118422 loops=1)
  Group Key: customer_id, status
  Batches: 1  Peak Memory Usage: 16401 kB
  ->  Seq Scan on orders  (actual rows=8000000 loops=1)

-- GROUP BY: identical plan, identical cost
HashAggregate  (actual time=1398.2..1484.7 rows=118422 loops=1)
  Group Key: customer_id, status

Now add an ORDER BY, and the two diverge:

EXPLAIN (ANALYZE) SELECT DISTINCT customer_id, status FROM orders ORDER BY customer_id;

Unique  (actual time=6120.4..7011.8 rows=118422 loops=1)
  ->  Sort  (actual time=6120.3..6702.1 rows=8000000 loops=1)
        Sort Key: customer_id, status
        Sort Method: external merge  Disk: 372160kB     -- 8M rows sorted to satisfy both clauses

The ordering requirement made a sort mandatory, so the planner folded de-duplication into a Unique above it rather than hashing first and sorting 118 k rows afterwards. Writing the same query as GROUP BY customer_id, status ORDER BY customer_id lets the planner hash 8 M rows down to 118 k and then sort only those — usually far cheaper.

The starkest difference is count(DISTINCT …):

-- Sorting happens INSIDE the aggregate; no parallelism, no hash
Aggregate  (actual time=9821.4..9821.4 rows=1 loops=1)
  ->  Seq Scan on orders  (actual rows=8000000 loops=1)
Execution Time: 9862.7 ms

-- Rewritten so the de-duplication is a normal grouping step
SELECT count(*) FROM (SELECT DISTINCT customer_id FROM orders) d;

Aggregate  (actual time=1502.1..1502.1 rows=1 loops=1)
  ->  HashAggregate  (actual rows=118422 loops=1)  Batches: 1
        ->  Gather  (actual rows=8000000 loops=1)  Workers Launched: 4
Execution Time: 1544.3 ms

Six times faster, because the rewrite exposes the de-duplication to HashAggregate and lets the scan beneath it run in parallel — see parallel query execution for why the original form blocks workers.

Step-by-Step Resolution Workflow #

  1. Plan both formulations of the query and compare node types, not just timings:

    EXPLAIN (ANALYZE, BUFFERS) SELECT DISTINCT customer_id, status FROM orders ORDER BY customer_id;
EXPLAIN (ANALYZE, BUFFERS) SELECT customer_id, status FROM orders GROUP BY 1,2 ORDER BY customer_id;

  2. Look for Unique above a Sort. That combination over a large input is the signal that de-duplication is happening the expensive way.

  3. Rewrite count(DISTINCT x) as count(*) over a grouped subquery whenever the input runs to millions of rows.

  4. Consider an index when the query genuinely needs order. An index on the distinct columns can make the plan an Index Only Scan feeding a cheap Unique, removing the sort entirely:

    CREATE INDEX CONCURRENTLY orders_customer_status_idx ON orders (customer_id, status);

  5. Leave DISTINCT ON alone but feed it an ordered path. It cannot be hashed, so the only lever is an index matching its sort key.

  6. Verify with buffers. The win shows up as the disappearance of Sort Method: external merge and its Disk: figure.

Relative cost of four ways to de-duplicate the same data Horizontal bars. Plain DISTINCT with a hash aggregate is fastest. GROUP BY with ORDER BY is close behind. DISTINCT with ORDER BY forcing a full sort is much slower. count DISTINCT is the slowest of the four. DISTINCT (hashed) 1489 ms GROUP BY + ORDER BY 1802 ms DISTINCT + ORDER BY 7012 ms count(DISTINCT x) 9863 ms 8 million input rows, 118,422 distinct pairs The rewrite ladder for de-duplication Four rungs from worst to best: count DISTINCT inside an aggregate, DISTINCT with ORDER BY forcing a full sort, GROUP BY with ordering applied after reduction, and an index-supplied ordering feeding a cheap unique step. count(DISTINCT x) — sort inside the aggregate DISTINCT + ORDER BY — full sort, then Unique GROUP BY — hash first, sort the 118k result indexed order — Unique over an Index Only Scan fastest slowest

Common Pitfalls #

Adding DISTINCT to hide a join fan-out. When a join multiplies rows, DISTINCT removes the duplicates at full cost instead of fixing the join. Diagnostic signal: input rows far above the table’s row count before the de-duplication node. Fix: use EXISTS or a semi join so duplicates are never produced — see CTE and subquery planning.

Assuming DISTINCT and GROUP BY always plan alike. They do for simple column lists with no ordering, and diverge as soon as ORDER BY, expressions, or aggregates appear. Diagnostic signal: Unique above Sort in one plan and HashAggregate in the other. Fix: test both forms rather than trusting a rule of thumb.

Leaving count(DISTINCT x) in a hot reporting query. It sorts inside the aggregate and blocks parallelism. Diagnostic signal: a bare Aggregate over a large Seq Scan with no Gather. Fix: rewrite as a count over a grouped subquery.

Expecting DISTINCT ON to hash. It has no hashed implementation. Diagnostic signal: a mandatory Sort you cannot remove with enable_sort tricks. Fix: build an index on the DISTINCT ON expressions plus the tiebreaker columns.

Frequently Asked Questions #

Is GROUP BY faster than SELECT DISTINCT? Frequently, but not by rule. Both can be hashed when the query is simple; the difference appears once an ORDER BY or an expression forces a sort into the DISTINCT path. Rewriting as GROUP BY lets the planner hash first and sort only the reduced result.

Why is DISTINCT ON slower than I expect? Because it always requires ordered input — first by the DISTINCT ON expressions, then by the ORDER BY list — and has no hashed implementation. Without an index that supplies exactly that ordering, a full sort is unavoidable.

Can count(DISTINCT x) use a hash table? Not in that form; the de-duplication happens inside the aggregate by sorting, which also prevents parallelism. Rewriting it as count(*) over SELECT DISTINCT x moves the work into a HashAggregate that can sit above a parallel scan.