Redshift Observatory

System Table Tracker

System view pg_catalog.pg_statio_all_tables version 1.0.71912 / 2024-08-02

schema name column data type
pg_catalog pg_statio_all_tables heap_blks_hit int8
pg_catalog pg_statio_all_tables heap_blks_read int8
pg_catalog pg_statio_all_tables idx_blks_hit int8
pg_catalog pg_statio_all_tables idx_blks_read int8
pg_catalog pg_statio_all_tables relid oid
pg_catalog pg_statio_all_tables relname name
pg_catalog pg_statio_all_tables schemaname name
pg_catalog pg_statio_all_tables tidx_blks_hit int8
pg_catalog pg_statio_all_tables tidx_blks_read int8
pg_catalog pg_statio_all_tables toast_blks_hit int8
pg_catalog pg_statio_all_tables toast_blks_read int8

View Text

SELECT c.oid AS relid,
       n.nspname AS schemaname,
       c.relname,
       pg_stat_get_blocks_fetched(c.oid) - pg_stat_get_blocks_hit(c.oid) AS heap_blks_read,
       pg_stat_get_blocks_hit(c.oid) AS heap_blks_hit,
       sum(pg_stat_get_blocks_fetched(i.indexrelid) - pg_stat_get_blocks_hit(i.indexrelid)) AS idx_blks_read,
       sum(pg_stat_get_blocks_hit(i.indexrelid)) AS idx_blks_hit,
       pg_stat_get_blocks_fetched(t.oid) - pg_stat_get_blocks_hit(t.oid) AS toast_blks_read,
       pg_stat_get_blocks_hit(t.oid) AS toast_blks_hit,
       pg_stat_get_blocks_fetched(x.oid) - pg_stat_get_blocks_hit(x.oid) AS tidx_blks_read,
       pg_stat_get_blocks_hit(x.oid) AS tidx_blks_hit
FROM pg_class AS c
     LEFT JOIN pg_index AS i ON c.oid = i.indrelid
     LEFT JOIN pg_class AS t ON c.reltoastrelid = t.oid
     LEFT JOIN pg_class AS x ON t.reltoastidxid = x.oid
     LEFT JOIN pg_namespace AS n ON n.oid = c.relnamespace
WHERE c.relkind = CAST('r' AS "char")
GROUP BY c.oid, n.nspname, c.relname, t.oid, x.oid


Home 3D Друк Blog Bring-Up Times Consultancy Cross-Region Benchmarks Email Forums IRC Mailing Lists Reddit Redshift Price Tracker Redshift Version Tracker Redshift Workbench System Table Tracker The Known Universe Twitter White Papers