Unique in PostgreSQL EXPLAIN

What it does

Unique reads rows that arrive sorted on the columns that must be distinct. It passes a row on only if it differs from the previous one; duplicates are next to each other in sorted input, so one comparison per row is enough. It keeps a single row in memory and streams its output.

It’s used for:

• SELECT DISTINCT, when the planner sorts rather than hashes.
• SELECT DISTINCT ON (a) … ORDER BY a, b: keeps the first row of each group of equal a. With the ORDER BY, that’s “the row with the smallest or largest b per a”, a common way to pick the latest row per customer.
• UNION (without ALL), on sorted input, to remove duplicates across branches.

The alternative for DISTINCT and UNION is a HashAggregate, which needs no sorted input but holds every distinct value in memory. DISTINCT ON always uses sorted input and Unique.

When the planner picks it

• The input is already in the right order, typically from an index on the distinct columns.
• The query also has ORDER BY on the same columns, so one sort serves both.
• DISTINCT ON, always.
• Many distinct values, where a hash table would be large.

Since PostgreSQL 15, SELECT DISTINCT can also be split across parallel workers.

Reading its numbers

Unique  (cost=0.42..243.99 rows=9243 width=4) (actual time=0.009..0.857 rows=500.00 loops=1)
  Buffers: shared hit=13
  ->  Index Only Scan using orders_customer_id_idx on orders  (… rows=10000.00 loops=1)

Unique has no fields of its own beyond the standard ones:

• rows: distinct rows kept. Compare with the child’s rows: here 10,000 in, 500 out.
• The estimate (rows=9243) comes from the column’s n_distinct statistics and is often rough, as here.
• actual time: mostly its child’s. The comparisons themselves are cheap.

When it’s a problem

1. Reading everything to find a few distinct values. SELECT DISTINCT customer_id FROM orders walks a million index entries to return 50,000 values. If there’s a table that already lists the values (here, customers), ask it instead: SELECT id FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id). In the example: 84 ms → 35 ms. With very few distinct values and no such table, a recursive query that jumps from one value to the next through the index (a “loose index scan”) is the usual workaround.
2. A big Sort under it. Sort Method: external merge beneath a Unique means the sort spilled. Raise work_mem for the query, or provide the order with an index on the distinct columns.
3. DISTINCT hiding a join problem. A DISTINCT added to remove duplicates created by a join often means the join should be an EXISTS. The duplicates cost time to create and more to remove.
4. DISTINCT ON without a supporting index sorts all candidate rows. An index on (customer_id, created_at DESC) gives it its order directly.

Example

PostgreSQL 18.6, default settings:

CREATE SCHEMA seo_explain;
SET search_path = seo_explain;

CREATE TABLE customers (
  id         int PRIMARY KEY,
  name       text NOT NULL,
  country    text NOT NULL,
  created_at timestamptz NOT NULL
);
INSERT INTO customers
SELECT i, 'Customer ' || i,
       (ARRAY['GB','US','DE','FR','NL','IE','ES','IT','SE','NO',
              'DK','FI','PL','PT','BE','AT','CH','CA','AU','NZ'])[1 + (i * 7) % 20],
       timestamptz '2023-01-01' + i * interval '20 minutes'
FROM generate_series(1, 50000) AS i;

CREATE TABLE orders (
  id          bigint PRIMARY KEY,
  customer_id int NOT NULL REFERENCES customers (id),
  status      text NOT NULL,
  total       numeric(10,2) NOT NULL,
  created_at  timestamptz NOT NULL
);
INSERT INTO orders
SELECT i,
       1 + (i::bigint * 7919) % 50000,
       CASE WHEN i % 100 < 90 THEN 'shipped'
            WHEN i % 100 < 97 THEN 'pending'
            ELSE 'refunded' END,
       round(((i * 37) % 100000) / 100.0, 2),
       timestamptz '2024-01-01' + i * interval '1 minute'
FROM generate_series(1, 1000000) AS i;
CREATE INDEX orders_customer_id_idx ON orders (customer_id);
CREATE INDEX orders_created_at_idx ON orders (created_at);
VACUUM ANALYZE customers, orders;

Which of the first 500 customers have orders:

EXPLAIN (ANALYZE, BUFFERS)
SELECT DISTINCT customer_id FROM orders WHERE customer_id <= 500;

Unique  (cost=0.42..243.99 rows=9243 width=4) (actual time=0.009..0.857 rows=500.00 loops=1)
  Buffers: shared hit=13
  ->  Index Only Scan using orders_customer_id_idx on orders  (cost=0.42..218.54 rows=10178 width=4) (actual time=0.008..0.460 rows=10000.00 loops=1)
        Index Cond: (customer_id <= 500)
        Heap Fetches: 0
        Index Searches: 1
        Buffers: shared hit=13
Planning Time: 0.063 ms
Execution Time: 0.881 ms

The index returns customer ids in order, so Unique only has to skip repeats.

The latest order of each of those customers, with DISTINCT ON:

EXPLAIN (ANALYZE, BUFFERS)
SELECT DISTINCT ON (customer_id) customer_id, id, total, created_at
FROM orders WHERE customer_id <= 500
ORDER BY customer_id, created_at DESC;

Unique  (cost=9693.16..9744.05 rows=9243 width=26) (actual time=23.991..27.158 rows=500.00 loops=1)
  Buffers: shared hit=8346
  ->  Sort  (cost=9693.16..9718.61 rows=10178 width=26) (actual time=23.988..25.080 rows=10000.00 loops=1)
        Sort Key: customer_id, created_at DESC
        Sort Method: quicksort  Memory: 853kB
        Buffers: shared hit=8346
        ->  Bitmap Heap Scan on orders  (cost=119.30..9015.65 rows=10178 width=26) (actual time=2.289..17.084 rows=10000.00 loops=1)
              Recheck Cond: (customer_id <= 500)
              Heap Blocks: exact=8334
              Buffers: shared hit=8346
              ->  Bitmap Index Scan on orders_customer_id_idx  (cost=0.00..116.76 rows=10178 width=0) (actual time=0.832..0.833 rows=10000.00 loops=1)
                    Index Cond: (customer_id <= 500)
                    Index Searches: 1
                    Buffers: shared hit=12
Planning Time: 0.114 ms
Execution Time: 27.282 ms

Here the index can’t provide created_at DESC within each customer, so the 10,000 candidate orders are fetched and sorted before Unique keeps the first per customer.

All customers with orders, two ways:

EXPLAIN (ANALYZE, BUFFERS) SELECT DISTINCT customer_id FROM orders;

Unique  (cost=0.42..21300.42 rows=49565 width=4) (actual time=0.012..82.111 rows=50000.00 loops=1)
  Buffers: shared hit=947
  ->  Index Only Scan using orders_customer_id_idx on orders  (cost=0.42..18800.42 rows=1000000 width=4) (actual time=0.011..45.350 rows=1000000.00 loops=1)
        Heap Fetches: 0
        Index Searches: 1
        Buffers: shared hit=947
Planning Time: 0.043 ms
Execution Time: 83.994 ms

EXPLAIN (ANALYZE, BUFFERS)
SELECT c.id FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

Gather  (cost=1000.72..21286.30 rows=49565 width=4) (actual time=0.233..33.661 rows=50000.00 loops=1)
  Workers Planned: 1
  Workers Launched: 1
  Buffers: shared hit=150006 read=137
  ->  Nested Loop Semi Join  (cost=0.71..15329.80 rows=29156 width=4) (actual time=0.071..28.825 rows=25000.00 loops=2)
        Buffers: shared hit=150006 read=137
        ->  Parallel Index Only Scan using customers_pkey on customers c  (cost=0.29..1100.41 rows=29412 width=4) (actual time=0.047..4.023 rows=25000.00 loops=2)
              Heap Fetches: 0
              Index Searches: 1
              Buffers: shared hit=5 read=135
        ->  Index Only Scan using orders_customer_id_idx on orders o  (cost=0.42..0.85 rows=20 width=4) (actual time=0.001..0.001 rows=1.00 loops=50000)
              Index Cond: (customer_id = c.id)
              Heap Fetches: 0
              Index Searches: 50000
              Buffers: shared hit=150001 read=2
Planning:
  Buffers: shared hit=43 read=7
Planning Time: 0.446 ms
Execution Time: 35.293 ms

The semi join stops at the first order of each customer (rows=1.00 per loop) instead of reading all twenty. It touches more pages (one short index descent per customer) but does far less work per customer, and runs in parallel.

In Inlet

Inlet draws EXPLAIN ANALYZE as a tree and highlights the slowest step and badly misestimated row counts.