Duplicate Index

Overview

Duplicate indexes are pairs where one index's key columns are a prefix of another on the same table. The shorter index is made redundant by the longer one for range scans, but both indexes are maintained on every write. This doubles (or more) write amplification and wastes storage.

The most common cause is two developers independently adding an index for the same column, or an ORM migration adding a redundant single-column index alongside an existing composite index that starts with the same column.

insightral detects exact duplicates and subset duplicates. It reports the pair so a human can decide which to keep—usually the one with a more specific WHERE clause or the one with more recent scans.

Detection SQL

Run this query with a read-only role. No writes, no locks.

-- Pairs of indexes on the same table covering the same leading columns
SELECT
  a.indrelid::regclass          AS table_name,
  a.indexrelid::regclass        AS index_a,
  b.indexrelid::regclass        AS index_b,
  pg_size_pretty(pg_relation_size(a.indexrelid)) AS size_a,
  pg_size_pretty(pg_relation_size(b.indexrelid)) AS size_b
FROM pg_index a
JOIN pg_index b
  ON a.indrelid = b.indrelid
  AND a.indexrelid < b.indexrelid
  AND a.indkey[0] = b.indkey[0]
  AND (
    a.indkey::int[] @> b.indkey::int[]
    OR b.indkey::int[] @> a.indkey::int[]
  )
WHERE NOT a.indisprimary
  AND NOT b.indisprimary;

Fix SQL

Replace placeholders in <angle brackets> with values from the detection output before running.

-- Keep the more selective / narrower index, drop the other
DROP INDEX CONCURRENTLY <duplicate_index_name>;

Worked example

Common false positives

• A unique index and a non-unique index on the same column serve different purposes — the unique index enforces a constraint and cannot be dropped even if a regular index covers it.
• Partial indexes (WITH WHERE) that overlap leading columns but cover different row subsets are not true duplicates.