postgresql-performance-check-queries.md

Check index usage statistics

-- Check index usage statistics --
SELECT 
    schemaname, 
    relname AS table_name, 
    indexrelname AS index_name, 
    idx_scan AS number_of_scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM 
    pg_stat_user_indexes
WHERE 
    schemaname = 'public'
ORDER BY 
    idx_scan DESC;

Identify unused indexes

-- Identify unused indexes --
SELECT 
    schemaname, 
    relname AS table_name, 
    indexrelname AS index_name, 
    idx_scan AS number_of_scans,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM 
    pg_stat_user_indexes
WHERE 
    schemaname = 'public' 
    AND idx_scan = 0 
    AND idx_tup_read = 0
ORDER BY 
    pg_relation_size(indexrelid) DESC;

Index size and usage statistics

-- Index size and usage statistics --
SELECT
    t.schemaname,
    t.tablename,
    c.reltuples AS rows,
    pg_size_pretty(pg_relation_size(c.oid)) AS table_size,
    idstat.indexrelname AS index_name,
    pg_size_pretty(pg_relation_size(idstat.indexrelid)) AS index_size,
    idstat.idx_scan AS index_scans
FROM
    pg_stat_user_tables AS t
    JOIN pg_class c ON t.relid = c.oid
    LEFT OUTER JOIN pg_stat_user_indexes AS idstat
        ON t.relid = idstat.relid
WHERE
    t.schemaname IN ('public', '_airbyte_public')
ORDER BY
    t.schemaname, t.tablename, idstat.indexrelname;

Duplicate indexes

-- Duplicate indexes --
SELECT 
    pg_size_pretty(SUM(pg_relation_size(idx))::BIGINT) AS SIZE,
    (array_agg(idx))[1] AS idx1, (array_agg(idx))[2] AS idx2,
    (array_agg(idx))[3] AS idx3, (array_agg(idx))[4] AS idx4
FROM (
    SELECT indexrelid::regclass AS idx, (indrelid::text ||E'\n'|| indclass::text ||E'\n'|| indkey::text ||E'\n'||
        COALESCE(indexprs::text,'')||E'\n' || COALESCE(indpred::text,'')) AS KEY
    FROM pg_index) sub
GROUP BY KEY HAVING COUNT(*)>1
ORDER BY SUM(pg_relation_size(idx)) DESC;

Index hit rate

-- Index hit rate --
SELECT 
    relname, 
    100 * idx_scan / (seq_scan + idx_scan) percent_of_times_index_used, 
    n_live_tup rows_in_table
FROM 
    pg_stat_user_tables
WHERE 
    schemaname IN ('public', '_airbyte_public')
    AND seq_scan + idx_scan > 0 
ORDER BY 
    n_live_tup DESC;

Tables with missing indexes (high sequential scan count)

-- Tables with missing indexes (high sequential scan count) --
SELECT
    schemaname,
    relname,
    seq_scan,
    seq_tup_read,
    idx_scan,
    seq_tup_read / NULLIF(seq_scan, 0) AS avg_seq_tuples_per_scan,
    idx_scan / NULLIF(seq_scan, 0) AS index_use_ratio
FROM
    pg_stat_user_tables
WHERE
    schemaname IN ('public', '_airbyte_public')
    AND seq_scan > 0
ORDER BY
    seq_tup_read DESC
LIMIT 25;

Index Monitoring Query with All Indexed Columns

-- Index Monitoring Query with All Indexed Columns --
SELECT 
    ut.schemaname, 
    ut.relname AS table_name, 
    ui.indexrelname AS index_name, 
    ui.idx_scan AS number_of_scans,
    pg_size_pretty(pg_relation_size(ui.indexrelid)) AS index_size,
    array_to_string(ARRAY(
        SELECT a.attname
        FROM unnest(i.indkey) WITH ORDINALITY AS cols(colnum, ordinality)
        JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = cols.colnum
        ORDER BY cols.ordinality
    ), ', ') AS indexed_columns,
    CASE 
        WHEN i.indisunique THEN 'UNIQUE'
        ELSE ''
    END AS uniqueness,
    pg_get_indexdef(i.indexrelid) AS index_definition
FROM 
    pg_stat_user_indexes ui
JOIN 
    pg_index i ON ui.indexrelid = i.indexrelid
JOIN 
    pg_attribute a ON ui.indexrelid = a.attrelid
JOIN
    pg_stat_user_tables ut ON ui.relid = ut.relid
WHERE 
    ut.schemaname = 'public'
    AND a.attnum = ANY(i.indkey)
    -- AND ut.relname IN ('runs', 'runs_scd', '_airbyte_raw_runs', 'orders', 'orders_scd', '_airbyte_raw_orders')
GROUP BY 
    ut.schemaname, ut.relname, ui.indexrelname, ui.idx_scan, ui.indexrelid, i.indisunique, i.indexrelid
ORDER BY 
    ut.relname,
    pg_relation_size(ui.indexrelid) DESC;