Find tables worth investigating for missing indexes

The first query follows DBA-022: it surfaces larger relations scanned sequentially more often than by index. It does not prove that an index is missing.

SELECT
    schemaname,
    relname AS table_name,
    seq_scan,
    idx_scan,
    n_live_tup,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
WHERE seq_scan > COALESCE(idx_scan, 0)
  AND n_live_tup > 10000
ORDER BY seq_scan DESC
LIMIT 20;

Choose thresholds appropriate to your workload. Large sequential-scan counts may be normal on reporting and analytics tables.

Find indexes with no recorded scans

This query excludes primary and unique indexes from a first-pass review. It does not make the remaining indexes safe to drop; indexes can support constraints, rare jobs or standby queries.

SELECT
    ui.schemaname,
    ui.relname AS table_name,
    ui.indexrelname AS index_name,
    ui.idx_scan,
    pg_size_pretty(pg_relation_size(ui.indexrelid)) AS index_size
FROM pg_stat_user_indexes AS ui
JOIN pg_index AS ix
  ON ix.indexrelid = ui.indexrelid
WHERE ui.idx_scan = 0
  AND NOT ix.indisprimary
  AND NOT ix.indisunique
ORDER BY pg_relation_size(ui.indexrelid) DESC
LIMIT 20;

Check when counters began accumulating before treating zero as meaningful:

SELECT datname, stats_reset
FROM pg_stat_database
WHERE datname = current_database();

What the columns actually tell you

Turn a candidate into an evidence-backed decision

1. Identify the slow query. Look for the real predicate or join driving the table scan.
2. Read its EXPLAIN plan. Decide whether selective filtering could actually benefit from a suitable index.
3. Check workload frequency. Daily batch jobs and seasonal reporting can make a zero-scan index useful.
4. Check dependencies. Constraints, foreign keys and other operational uses require separate review.
5. Test the change. Measure performance and write overhead in a representative environment before any removal or production deployment.

Related DBA tasks

DBA-021 (read a plan), DBA-023 (build an index concurrently), DBA-026 (rank expensive queries), and DBA-027 (evaluate sequential scans) help refine this investigation.