PostgreSQL Index Usage Review

Purpose

Reviews index usage against PostgreSQL statistics views to identify:

1. Unused indexes (no scans since last statistics reset)
2. Indexes with high write overhead and low read use
3. Tables with high sequential scan rates (potential missing index candidates)
4. Duplicate index candidates

Required Permissions

SELECT on pg_stat_user_indexes, pg_stat_user_tables, and pg_indexes.

Standard pg_monitor role is sufficient.

Risk Level

Low. Read-only. However, decisions made based on this output (dropping indexes) are irreversible without replication of the index — treat the output as investigation input, not action triggers.

Preconditions

• Statistics must have accumulated for a meaningful period. Results from a freshly started instance or recently reset statistics will incorrectly classify all indexes as unused.
• Check when statistics were last reset: SELECT stats_reset FROM pg_stat_bgwriter;
• Minimum recommended statistics age: 7 days. 30 days is more reliable.

Script

-- ============================================================
-- PostgreSQL Index Usage Review
-- Permissions: pg_monitor or SELECT on pg_stat_user_indexes
-- Risk: Read-only (the script itself)
-- NOTE: Do not drop indexes based solely on this output.
--       Verify statistics age and consider seasonal workloads.
-- ============================================================

-- 1. Unused indexes (never scanned since statistics reset)
--    Excludes primary key indexes (always used for constraint enforcement)
SELECT
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid::regclass)) AS index_size,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT IN (
      SELECT conname FROM pg_constraint WHERE contype = 'p'
  )
ORDER BY pg_relation_size(indexrelid::regclass) DESC;

-- 2. Low-usage indexes with high write cost
--    (many index writes, few index reads)
SELECT
    psi.schemaname,
    psi.tablename,
    psi.indexname,
    pg_size_pretty(pg_relation_size(psi.indexrelid::regclass)) AS index_size,
    psi.idx_scan,
    pst.n_tup_ins + pst.n_tup_upd + pst.n_tup_del               AS total_writes,
    CASE WHEN (pst.n_tup_ins + pst.n_tup_upd + pst.n_tup_del) > 0
        THEN ROUND(
            CAST(psi.idx_scan AS NUMERIC) /
            (pst.n_tup_ins + pst.n_tup_upd + pst.n_tup_del),
            4
        )
    END                                                           AS scan_per_write_ratio
FROM pg_stat_user_indexes    AS psi
JOIN pg_stat_user_tables     AS pst ON psi.relid = pst.relid
WHERE pst.n_tup_ins + pst.n_tup_upd + pst.n_tup_del > 10000
  AND psi.idx_scan < 100
ORDER BY total_writes DESC;

-- 3. Tables with high sequential scan rates (potential missing index)
SELECT
    schemaname,
    relname                                                        AS table_name,
    seq_scan,
    seq_tup_read,
    idx_scan,
    n_live_tup                                                     AS live_rows,
    CASE WHEN seq_scan + idx_scan > 0
        THEN ROUND(100.0 * seq_scan / (seq_scan + idx_scan), 1)
    END                                                            AS seq_scan_pct
FROM pg_stat_user_tables
WHERE seq_scan > 1000
  AND n_live_tup > 10000
ORDER BY seq_scan DESC
LIMIT 20;

-- 4. Index size summary by table
SELECT
    t.schemaname,
    t.relname                                                      AS table_name,
    pg_size_pretty(pg_total_relation_size(t.relid::regclass))     AS total_size,
    pg_size_pretty(pg_relation_size(t.relid::regclass))           AS table_size,
    pg_size_pretty(
        pg_total_relation_size(t.relid::regclass)
        - pg_relation_size(t.relid::regclass)
    )                                                              AS index_size,
    COUNT(i.indexrelid)                                            AS index_count
FROM pg_stat_user_tables  AS t
JOIN pg_stat_user_indexes AS i ON t.relid = i.relid
GROUP BY t.schemaname, t.relname, t.relid
ORDER BY pg_total_relation_size(t.relid::regclass) DESC
LIMIT 20;

Interpreting Results

Unused indexes: An index with idx_scan = 0 over 30 days of production load is a candidate for removal. Verify the statistics age before acting. Keep primary key indexes — they are required for constraint enforcement even if idx_scan is 0.

Low scan-to-write ratio: An index costing many writes per read is a candidate for removal on write-heavy tables. The scan-per-write threshold depends on the workload — for OLTP, a ratio below 0.01 (1 scan per 100 writes) suggests the index adds more overhead than value.

High sequential scans: Tables with >90% sequential scan rate and >10,000 live rows are worth examining for missing indexes. Check the actual query patterns before creating an index.

Safe Execution Guidance

Do not drop indexes without:

1. Confirming statistics age is sufficient
2. Considering seasonal workloads (an annual report may drive index use that does not appear in a 30-day sample)
3. Testing the drop in a non-production environment
4. Having a script ready to recreate the index with CREATE INDEX CONCURRENTLY if needed

Rollback Steps

If an index is dropped and found to be needed:

CREATE INDEX CONCURRENTLY idx_name ON schema.table (column);

CONCURRENTLY builds the index without blocking reads and writes. It takes longer than a standard build but is safe for production use.