PostgreSQL Exact Duplicate Index Finder

Purpose

Identifies indexes on the same table that have identical key column lists and identical INCLUDE column lists. Duplicate indexes in PostgreSQL impose write amplification on every INSERT, UPDATE, and DELETE against the indexed table and consume additional shared buffer and I/O resources — without providing any additional query coverage.

The script separates key columns (ordered by position) from INCLUDE columns (PostgreSQL 11+ non-key columns) using pg_index.indnkeyatts and pg_index.indnatts. Signatures are built with STRING_AGG and self-joined to surface pairs. Each row includes uniqueness, primary key status, and the index access method (btree, hash, gin, etc.) to assist with the decision on which duplicate to retain.

Supported Platforms and Versions

PostgreSQL 13 through 18. Uses pg_index, pg_class, pg_attribute, and pg_namespace — all standard catalog views. The INCLUDE column separation uses indnkeyatts, available since PostgreSQL 11.

Required Permissions

SELECT on pg_catalog views — available to any database user by default.

Preconditions

Run connected to the target database. The script filters out pg_catalog, information_schema, and pg_toast schemas. It only surfaces indexes on ordinary tables (not views, foreign tables, or partitioned table indexes at the partition level).

Risk Level

Low. Read-only. No indexes are dropped. Identifies candidates only.

Script

-- ============================================================
-- PostgreSQL Exact Duplicate Index Finder
-- Permissions : SELECT on pg_catalog (any user)
-- Risk        : Read-only — identifies candidates, drops nothing
-- Platform    : PostgreSQL 13+
-- ============================================================

WITH index_key AS
(
    -- Key columns only: positions 1 through indnkeyatts
    SELECT
        i.indexrelid,
        STRING_AGG(a.attname, ',' ORDER BY k.ord)   AS key_cols
    FROM pg_index AS i
    CROSS JOIN LATERAL
        unnest(i.indkey::int2[]) WITH ORDINALITY AS k(attnum, ord)
    JOIN pg_attribute AS a
        ON a.attrelid = i.indrelid AND a.attnum = k.attnum
    WHERE k.ord      <= i.indnkeyatts
      AND k.attnum    > 0               -- exclude expression-index placeholder (attnum 0)
      AND NOT a.attisdropped
    GROUP BY i.indexrelid
),
index_inc AS
(
    -- INCLUDE columns only: positions indnkeyatts+1 through indnatts (pg 11+)
    -- Returns empty string if no INCLUDE columns exist on this index.
    SELECT
        i.indexrelid,
        COALESCE(
            STRING_AGG(a.attname, ',' ORDER BY a.attname),
            '')                                      AS inc_cols
    FROM pg_index AS i
    CROSS JOIN LATERAL
        unnest(i.indkey::int2[]) WITH ORDINALITY AS k(attnum, ord)
    JOIN pg_attribute AS a
        ON a.attrelid = i.indrelid AND a.attnum = k.attnum
    WHERE k.ord       > i.indnkeyatts
      AND k.attnum    > 0
      AND NOT a.attisdropped
    GROUP BY i.indexrelid
),
index_info AS
(
    SELECT
        i.indexrelid,
        i.indrelid,
        ci.relname                              AS index_name,
        ct.relname                              AS table_name,
        n.nspname                               AS schema_name,
        am.amname                               AS access_method,
        i.indisunique,
        i.indisprimary,
        i.indisvalid,
        k.key_cols,
        COALESCE(inc.inc_cols, '')              AS inc_cols
    FROM pg_index        AS i
    JOIN pg_class        AS ci ON ci.oid = i.indexrelid
    JOIN pg_class        AS ct ON ct.oid = i.indrelid
    JOIN pg_namespace    AS n  ON n.oid  = ct.relnamespace
    JOIN pg_am           AS am ON am.oid = ci.relam
    JOIN index_key       AS k  ON k.indexrelid  = i.indexrelid
    LEFT JOIN index_inc  AS inc ON inc.indexrelid = i.indexrelid
    WHERE n.nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
      AND ct.relkind = 'r'              -- ordinary tables only
)
SELECT
    ii1.schema_name || '.' || ii1.table_name        AS "table",
    ii1.index_name,
    ii1.access_method,
    ii1.indisunique                                  AS is_unique,
    ii1.indisprimary                                 AS is_primary,
    ii1.indisvalid                                   AS is_valid,
    ii1.key_cols,
    ii1.inc_cols,
    ii2.index_name                                   AS duplicate_index,
    ii2.access_method                                AS duplicate_access_method,
    ii2.indisunique                                  AS duplicate_is_unique,
    ii2.indisprimary                                 AS duplicate_is_primary,
    ii2.indisvalid                                   AS duplicate_is_valid
FROM index_info AS ii1
JOIN index_info AS ii2
    ON  ii1.indrelid     = ii2.indrelid
    AND ii1.indexrelid   < ii2.indexrelid
    AND ii1.key_cols     = ii2.key_cols
    AND ii1.inc_cols     = ii2.inc_cols
ORDER BY ii1.schema_name, ii1.table_name, ii1.key_cols;

Safe Execution Guidance

1. Run connected to the target database in psql or any PostgreSQL client.
2. Each row represents one duplicate pair. Both indexes cover the same queries — one can be dropped.
3. Before dropping, check which index has been used recently:

   SELECT
       schemaname,
       tablename,
       indexname,
       idx_scan,
       idx_tup_read,
       idx_tup_fetch,
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
   FROM pg_stat_user_indexes
   WHERE indexname IN ('index_a', 'duplicate_index')
   ORDER BY idx_scan;

   Prefer retaining the index with more recent or higher idx_scan and dropping the one with lower usage.
4. Check whether the index is used to enforce a constraint:

   -- Primary key and unique constraints have backing indexes that cannot be dropped independently.
   SELECT conname, contype FROM pg_constraint
   WHERE conindid = (SELECT oid FROM pg_class WHERE relname = '<index_name>');

   Constraint-backed indexes must be dropped via ALTER TABLE DROP CONSTRAINT, not DROP INDEX.
5. Generate candidate DROP INDEX CONCURRENTLY statements:

   SELECT 'DROP INDEX CONCURRENTLY ' || quote_ident(ii2.schema_name)
       || '.' || quote_ident(ii2.index_name) || ';' AS drop_statement
   FROM index_info AS ii1
   JOIN index_info AS ii2
       ON ii1.indrelid = ii2.indrelid AND ii1.indexrelid < ii2.indexrelid
       AND ii1.key_cols = ii2.key_cols AND ii1.inc_cols = ii2.inc_cols
   WHERE NOT ii2.indisprimary;
   -- Review and execute only after confirming low usage and no constraint dependency.

Expected Output

One row per duplicate pair. An empty result set means no exact duplicates exist in the current database (excluding system schemas).

Interpreting Results

Both indexes non-unique, non-primary: Either can be dropped. Use pg_stat_user_indexes.idx_scan to identify the lower-traffic one.

One index is a primary key: The primary key index cannot be dropped independently — it is the enforcement mechanism for the PRIMARY KEY constraint. The non-primary duplicate is the correct candidate for removal.

is_valid = false: An index in INVALID state (typically from a failed or in-progress CREATE INDEX CONCURRENTLY) should be dropped regardless. REINDEX CONCURRENTLY or DROP INDEX CONCURRENTLY followed by a fresh build is the correct remediation.

idx_scan counters are zero for both: The counters reset on pg_stat_reset() and on server restart. Zero scans does not necessarily mean the index is never used — confirm the observation window covers at least one full application workload cycle.

Partial indexes: This script does not treat partial indexes (those with a WHERE clause) as duplicates of full indexes even if the key columns match. A partial index is not functionally equivalent to a full index. If partial indexes are appearing in results, verify their predicate clauses manually:

SELECT indexname, indexdef FROM pg_indexes
WHERE indexname IN ('<index>', '<duplicate>');

Supplement: Index Sizes

SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

Rollback Steps

Not applicable — read-only script.

Official References

• pg_index
• pg_stat_user_indexes
• DROP INDEX
• CREATE INDEX — INCLUDE clause