Sums the number of index scans and sequential scans performed on TimescaleDB hypertables to identify potentially missing indexes (tables with a significant number of sequential scans).

hypertable_missing_indexes.sql

SELECT
    ht.schema_name,
    ht.table_name,
    COUNT(DISTINCT ck.table_name) AS nb_of_chunks,
    SUM(stat.seq_scan) AS seq_scan,
    SUM(stat.idx_scan) AS idx_scan,
    SUM(stat.idx_scan) - SUM(stat.seq_scan) AS difference,
    CASE WHEN SUM(stat.idx_scan) < SUM(stat.seq_scan) THEN 'Missing index ?' ELSE 'OK' END AS missing_index
FROM _timescaledb_catalog.hypertable ht
JOIN _timescaledb_catalog.chunk ck ON ht.id = ck.hypertable_id
JOIN pg_stat_all_tables stat ON stat.schemaname = ck.schema_name AND stat.relname = ck.table_name
GROUP BY 1, 2
ORDER BY difference DESC