-- perform a "select pg_stat_reset();" when you want to reset counter statistics
with
all_tables as
(
SELECT *
FROM (
SELECT 'all'::text as table_name,
sum( (coalesce(heap_blks_read,0) + coalesce(idx_blks_read,0) + coalesce(toast_blks_read,0) + coalesce(tidx_blks_read,0)) ) as from_disk,
sum( (coalesce(heap_blks_hit,0) + coalesce(idx_blks_hit,0) + coalesce(toast_blks_hit,0) + coalesce(tidx_blks_hit,0)) ) as from_cache
FROM pg_statio_all_tables --> change to pg_statio_USER_tables if you want to check only user tables (excluding postgres's own tables)
) a
WHERE (from_disk + from_cache) > 0 -- discard tables without hits
),
tables as
(
SELECT *
FROM (
SELECT relname as table_name,
( (coalesce(heap_blks_read,0) + coalesce(idx_blks_read,0) + coalesce(toast_blks_read,0) + coalesce(tidx_blks_read,0)) ) as from_disk,
( (coalesce(heap_blks_hit,0) + coalesce(idx_blks_hit,0) + coalesce(toast_blks_hit,0) + coalesce(tidx_blks_hit,0)) ) as from_cache
FROM pg_statio_all_tables --> change to pg_statio_USER_tables if you want to check only user tables (excluding postgres's own tables)
) a
WHERE (from_disk + from_cache) > 0 -- discard tables without hits
)
SELECT table_name as "table name",
from_disk as "disk hits",
round((from_disk::numeric / (from_disk + from_cache)::numeric)*100.0,2) as "% disk hits",
round((from_cache::numeric / (from_disk + from_cache)::numeric)*100.0,2) as "% cache hits",
(from_disk + from_cache) as "total hits"
FROM (SELECT * FROM all_tables UNION ALL SELECT * FROM tables) a
ORDER BY (case when table_name = 'all' then 0 else 1 end), from_disk desc
4条答案
按热度按时间t30tvxxf1#
从PostgreSQL 9.0开始,您可以执行:
它会告诉你语句是如何与PostgreSQL的缓存交互的。如果报告缓存缺失,那就是操作系统调用来读取一些东西。你不能确定这是物理I/O,因为它可能在操作系统缓存中。但这可能更像你在这里寻找的,而不是试图查看pg_stat_* 信息。
wa7juj8i2#
这个答案与特定的查询语句没有直接关系,而是为了帮助那些在这里结束的人寻找一种显示“磁盘与缓存”的方法:
vuktfyat3#
不幸的是,
PostgreSQL
没有像SET STATISTICS IO ON
那样简单。但是,通过pg_statio_*
系统目录可以获得IO统计信息。由于数据没有限定在会话范围内,因此它并不完美,但如果您希望了解查询的效率以及在洁净室环境中的效率,它可以很好地解决大多数问题。http://www.postgresql.org/docs/current/static/monitoring-stats.html
vkc1a9a24#
不完全是,因为PostgreSQL也严重依赖于操作系统缓存,它无法知道那里发生了什么。pg_catalog中的pg_statio* 视图家族保持着命中和实际读取的计数,但这些读取可能已经命中了操作系统缓存。