Unique Indexes and Planner Uniqueness Proofs #

Two databases hold the same sku values in products, unique in both. One has CREATE UNIQUE INDEX products_sku_key, the other a plain CREATE INDEX products_sku_idx because a migration tool generated it that way. Queries joining on sku are consistently faster on the first, EXPLAIN (VERBOSE) shows Inner Unique: true there and not on the second, and a view with an optional join to products is simpler in the first plan. The data is identical; the planner’s knowledge is not.

B-tree indexes are usually discussed as access paths, as in B-tree index optimization. A unique index is also a proof, and the planner uses that proof in several places that have nothing to do with scanning the index.

The Planner Condition #

The planner treats a relation’s columns as unique when there is a unique index (including primary keys and unique constraints) whose columns are all equated by conditions in the query, and the index is not partial or its predicate is implied by the query. It also derives uniqueness for subqueries with DISTINCT or GROUP BY on the relevant columns. It does not infer uniqueness from data, from statistics, or from a non-unique index that happens to contain unique values.

Uses of that proof:

What a unique index proves that a plain index does not A table comparing a unique index with a non-unique index on the same unique values. Only the unique index enables Inner Unique joins, LEFT JOIN removal and semi-join conversion without deduplication, and guarantees a one-row estimate for equality. Both provide the same access path for scans. UNIQUE index plain index, unique data access path for scans yes yes Inner Unique joins yes no LEFT JOIN removal yes no semi-join as plain join yes needs dedup step equality estimate exactly 1 row from statistics the planner trusts constraints, never observed data

Annotated EXPLAIN Evidence #

EXPLAIN (ANALYZE, VERBOSE)
SELECT l.order_id, p.name
FROM order_lines l
JOIN products p ON p.sku = l.sku
WHERE l.shipped_on = '2026-09-16';

With a plain index on products.sku:

Hash Join  (cost=48204.12..91204.44 rows=402110 width=40) (actual time=812.4..1904.6 rows=398204 loops=1)
  Output: l.order_id, p.name
  Hash Cond: (l.sku = p.sku)
  ->  Index Scan using order_lines_shipped_on_idx on public.order_lines l  (actual rows=398204 loops=1)
  ->  Hash  (actual rows=1204110 loops=1)
        ->  Seq Scan on public.products p
Execution Time: 1920.2 ms
-- no "Inner Unique": every probe walks the full bucket chain in case of more matches

With a unique index:

Hash Join  (cost=48204.12..84102.10 rows=398411 width=40) (actual time=792.6..1504.8 rows=398204 loops=1)
  Output: l.order_id, p.name
  Inner Unique: true
  Hash Cond: (l.sku = p.sku)
  ->  Index Scan using order_lines_shipped_on_idx on public.order_lines l  (actual rows=398204 loops=1)
  ->  Hash  (actual rows=1204110 loops=1)
        ->  Seq Scan on public.products p
Execution Time: 1518.4 ms
-- Inner Unique: probes stop at the first match; lower cost, ~20% faster here
Lines that show the proof in use Three lines are annotated. Inner Unique true in VERBOSE output shows the executor may stop after one match per outer row. A lower total cost on the same join shows the planner priced the shortcut. A relation missing from a plan with an unused LEFT JOIN shows join removal, which also depends on uniqueness. Inner Unique: true stop after first inner match cost=…84102.10 vs …91204.44 join priced cheaper with the proof (LEFT JOIN relation absent from plan) join removed: unique + unused Inner Unique is only printed with EXPLAIN (VERBOSE)

Step-by-Step Resolution #

  1. Find join keys that are unique in practice but not declared unique:

    SELECT count(*), count(DISTINCT sku) FROM products;
SELECT indexrelid::regclass, indisunique FROM pg_index WHERE indrelid = 'products'::regclass;

  2. Look for missing Inner Unique: true in EXPLAIN (VERBOSE) on joins to lookup and dimension tables.

  3. Create the unique index without blocking writes, then attach it as a constraint if you want it visible in the schema:

    CREATE UNIQUE INDEX CONCURRENTLY products_sku_key ON products (sku);
ALTER TABLE products ADD CONSTRAINT products_sku_unique UNIQUE USING INDEX products_sku_key;

    A concurrent build fails if duplicates exist; resolve them first. A failed concurrent build leaves an invalid index behind — see invalid indexes after failed concurrent builds.

  4. Drop the redundant non-unique index on the same columns; the unique index serves the same scans.

  5. Prefer full over partial unique indexes where the business rule allows; partial ones prove uniqueness only when the query implies their predicate.

  6. Re-check the plans for Inner Unique, join removal in views, and semi-join shapes.

Measured effect of declaring uniqueness The hash join to products ran 1.92 seconds without a unique index and 1.52 seconds with one. A view query with an unused left join to products ran 640 milliseconds with the join and 418 milliseconds after it was removed. An IN subquery on sku ran 2.4 seconds with a deduplication step and 1.6 seconds as a plain join. hash join, plain index 1,920 ms hash join, unique index 1,518 ms view, join kept 640 ms view, join removed 418 ms modest per query, but it applies to every join on the key

Before and After #

-- BEFORE: CREATE INDEX products_sku_idx ON products (sku)
Hash Join  Hash Cond: (l.sku = p.sku)                          Execution Time: 1920.2 ms

-- AFTER: CREATE UNIQUE INDEX products_sku_key ON products (sku)
Hash Join  Inner Unique: true  Hash Cond: (l.sku = p.sku)      Execution Time: 1518.4 ms

Uniqueness across multiple columns #

Proofs require the query to equate all columns of the unique index. A unique index on (tenant_id, sku) proves uniqueness for ON p.tenant_id = l.tenant_id AND p.sku = l.sku, but not for ON p.sku = l.sku alone. Multi-tenant schemas often join on the natural key without the tenant column because the application never mixes tenants — and lose every uniqueness-based optimisation as a result. Including the tenant column in join conditions costs nothing, keeps the proof valid, and often helps partition pruning and row-level security too.

Nullable columns are another subtlety. A unique index permits many rows with null in the key, but nulls never satisfy an equality join condition, so the proof still holds for joins. PostgreSQL 15 added UNIQUE NULLS NOT DISTINCT for cases where a single null should also be unique; it does not change join behaviour, only which inserts are allowed.

Common Pitfalls #

Relying on application-enforced uniqueness. The planner cannot see it. Diagnostic signal: no Inner Unique on joins to lookup tables. Fix: declare a unique index or constraint.

Joining on part of a composite unique key. The proof needs all columns. Diagnostic signal: uniqueness optimisations missing in multi-tenant joins. Fix: join on every key column.

Partial unique indexes. They prove nothing unless the query implies the predicate. Diagnostic signal: join removal failing despite a unique partial index. Fix: a full unique index where valid.

Keeping both unique and non-unique indexes. Duplicate write cost for no benefit. Diagnostic signal: two indexes on the same columns. Fix: drop the non-unique one.

Frequently Asked Questions #

What does Inner Unique mean in EXPLAIN? #

It means the planner proved that each outer row can match at most one row from the inner side of the join, usually through a unique index on the join key. The executor stops looking for further matches after the first one. It appears only in EXPLAIN VERBOSE output.

Does a non-unique index on unique data give the same benefits? #

No. The planner only trusts declared uniqueness from unique indexes, primary keys and unique constraints. A plain index provides the same access path but none of the uniqueness-based optimisations.

Can a unique index make joins faster even if it is not scanned? #

Yes. The uniqueness proof enables Inner Unique execution, join removal and simpler semi-join plans even when the join uses a hash or merge strategy and never reads the index itself.

Up: B-Tree Index Optimization