Duplicate & Redundant Indexes

Duplicate indexes waste disk, slow writes, and confuse DBAs. Audit catalogs and pg_stat_user_indexes before adding yet another composite on PostgreSQL 18.4.

Recipe

Quick-reference recipe card - copy-paste ready.

SELECT indexrelid::regclass AS index_name, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
WHERE schemaname = 'public' AND relname = 'orders'
ORDER BY idx_scan;

When to reach for this: Table has many indexes, insert latency climbs, or autovacuum cannot keep up.

Working Example

CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
 
-- Redundant: left prefix of composite
CREATE INDEX orders_customer_idx ON orders (customer_id);
CREATE INDEX orders_customer_created_idx ON orders (customer_id, created_at);
 
-- Duplicate intent (same columns, different name)
CREATE INDEX orders_cust_created_idx ON orders (customer_id, created_at);
 
SELECT
  c.relname AS table_name,
  i.relname AS index_name,
  pg_get_indexdef(i.oid) AS definition,
  s.idx_scan
FROM pg_class c
JOIN pg_index ix ON ix.indrelid = c.oid
JOIN pg_class i ON i.oid = ix.indexrelid
LEFT JOIN pg_stat_user_indexes s ON s.indexrelid = i.oid
WHERE c.relname = 'orders'
ORDER BY pg_get_indexdef(i.oid);

Drop orders_customer_idx if all queries needing customer_id also filter or sort on created_at, or keep the single-column index only when prefix-only queries exist and show idx_scan > 0.

What this demonstrates:

• Prefix redundancy detection via pg_get_indexdef
• idx_scan evidence before drop
• Naming collisions hiding duplicates

Deep Dive

How It Works

• Index A is prefix-redundant of B if A's key columns are a left prefix of B's and no unique constraint requires A alone.
• Duplicate indexes have identical key definitions (possibly different names from repeated migrations).
• Primary key and UNIQUE constraints create indexes; do not duplicate with plain INDEX on same columns.
• Unused indexes (idx_scan = 0 since stats reset) are drop candidates after observation window.

Audit Queries

-- Find never-used indexes (since stats reset)
SELECT relname, indexrelid::regclass, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND schemaname NOT IN ('pg_catalog', 'information_schema');
 
-- Size ranking
SELECT indexrelid::regclass, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

Gotchas

• Dropping prefix index while ORM uses only equality on first column - Plan may regress. Fix: Check pg_stat_statements and idx_scan.
• Dropping UNIQUE index thinking it is redundant - Constraint drops too. Fix: Drop constraint or re-add UNIQUE on remaining index.
• Resetting stats then immediate drop - idx_scan zero misleads. Fix: Observe over a business cycle.
• Replica-only usage - Stats on primary may not reflect reporting queries on replica if not routed. Fix: Aggregate stats from replicas too.
• Concurrent duplicate creation - Migration tools rerun. Fix: Idempotent migrations with IF NOT EXISTS.

Alternatives

FAQs

Related

• Multicolumn Index Order - Prefix rules
• REINDEX & REINDEX CONCURRENTLY - Rebuild after churn
• Table & Index Bloat - Size measurement
• Index Maintenance Best Practices - Ongoing hygiene

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.