SQL cache and index performance

cache_hit_rate.sql

SELECT
    'index hit rate' as name,
    (sum(idx_blks_hit) - sum(idx_blks_read)) /
    sum(idx_blks_hit + idx_blks_read) as ratio
    FROM pg_statio_user_indexes
    union all
        SELECT
            'cache hit rate' as name,
            case sum(idx_blks_hit)
                when 0 then 'NaN'::numeric
                else to_char((sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit + idx_blks_read), '99.99')::numeric
            end as ratio
        FROM pg_statio_user_indexes;

index_usage.sql

SELECT
    relname,
    100 * idx_scan / (seq_scan + idx_scan) as percent_of_time_index_used,
    n_live_tup as rows_in_table
    FROM pg_stat_user_tables
    WHERE idx_scan > 0
    ORDER BY n_live_tup DESC;