Check the table statistics to see index usage in PostgreSQL

Leave a reply

Understanding PostgreSQL Index Performance Statistics

Check table statistics to see index usage

Note: Query helps identify whether tables are using indexes effectively or relying mostly on sequential scans.

SELECT 
    relname,
    seq_scan,
    seq_tup_read,
    idx_scan,
    idx_tup_fetch,
    CASE 
        WHEN idx_scan > seq_scan THEN 'INDEX OPTIMIZED'
        WHEN idx_scan > 0 THEN 'MIXED ACCESS'
        ELSE 'NEEDS INDEXES'
    END as performance_status
FROM pg_stat_all_tables
WHERE schemaname = 'schemaname'
ORDER BY seq_scan + idx_scan DESC;
ColumnMeaning
relnameTable name
seq_scanNumber of sequential (full table) scans
seq_tup_readRows read by sequential scans
idx_scanNumber of index scans
idx_tup_fetchRows fetched using indexes
performance_statusCustom status based on scan usage

Note: A high seq_scan is not always bad. PostgreSQL may intentionally choose a sequential scan when:

Statistics indicate an index would be slower.

The table is small.

A query returns most rows.

2. Find Tables Mostly Using Sequential Scans

SELECT
    relname,
    seq_scan,
    idx_scan,
    n_live_tup
FROM pg_stat_user_tables
WHERE seq_scan > idx_scan
ORDER BY seq_scan DESC;