Is there any way to get postgres show actual i / o for request

Question

I know that with EXPLAIN ANALYZE I can get the predicted cost and actual runtime (which are in different units, argh!), But is there a way to get Postgres to tell me how many I / O (logical or physical) that it should do to satisfy the request?

(I'm looking for the equivalent of "set statistics io on" for Sybase or MS SQL Server.)

Answer 1

Starting with PostgreSQL 9.0, you can run:

EXPLAIN (ANALYZE ON, BUFFERS ON) SELECT ... 

And it will show you how the operator interacts with the PostgreSQL cache. In cases where it reports a cache miss, it will be an OS call to read. You cannot be sure of physical I / O, because it may be in the OS cache. But this is probably more like what you are looking for here than it is looking for information pg_stat_ *.

Answer 2

This answer is not directly related to a specific request, but helps those who have finished here when looking for a way to display "disk vs cache":

 -- 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 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 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 

Answer 3

Unfortunately, there is nothing simple like SET STATISTICS IO ON for PostgreSQL . However, IO statistics are available through the pg_statio_* system directories. This is not ideal, since the data is not tied to the session, but if you want to see how efficient the queries are and are in a clean room, it works well enough for most problems.

http://www.postgresql.org/docs/current/static/monitoring-stats.html

Answer 4

Not like PostgreSQL is also heavily dependent on the OS cache, and it does not know what is going on there. The pg_statio * view family in pg_catalog stores the number of hit counts and actual reads, but these reads may have hit the OS cache.