Postgresql - Volume de sequential scan / index scan par table

seq_scan_index_scan_per_table.sql

WITH stats_reset AS (
        SELECT EXTRACT(
            EPOCH FROM (
                CURRENT_TIMESTAMP -
                (
                    SELECT stats_reset FROM pg_stat_statements_info
                )
            )
        ) / 3600 AS hours_since_stats_reset
),

chunk_catalog AS (
    SELECT
        ch.table_name AS chunk_name,
        ht.table_name AS hypertable_name
    FROM _timescaledb_catalog.chunk ch
    JOIN _timescaledb_catalog.hypertable ht
    ON ht.id = ch.hypertable_id
)

SELECT
    COALESCE(chunk_catalog.hypertable_name, pg_stat_user_tables.relname) as table,
    pg_size_pretty(SUM(pg_relation_size(relid::regclass))) AS size,
    SUM(COALESCE(seq_scan, 0)) as sequential_scans,
    SUM(COALESCE(idx_scan, 0)) as index_scans,
    SUM(COALESCE(seq_scan, 0)) / NULLIF((SUM(COALESCE(seq_scan, 0)) + SUM(COALESCE(idx_scan, 0))), 0) AS share_of_seq_scans,
    pg_size_pretty(SUM(pg_relation_size(relid::regclass) * COALESCE(seq_scan, 0)) / (SELECT hours_since_stats_reset FROM stats_reset)::numeric) AS seq_scan_per_hour,
    CASE WHEN SUM(COALESCE(seq_scan, 0)) - SUM(COALESCE(idx_scan, 0)) > 0 THEN
        'Missing Index?'
    ELSE
        'OK'
    END AS status
FROM pg_stat_user_tables
LEFT JOIN chunk_catalog
ON chunk_catalog.chunk_name = pg_stat_user_tables.relname
WHERE pg_relation_size(relid::regclass) > 100000
GROUP BY 1
--ORDER BY SUM(pg_relation_size(relid::regclass)) DESC
ORDER BY SUM(pg_relation_size(relid::regclass) * COALESCE(seq_scan, 0)) DESC;