Probably this can be done with pg_stat_kcache. Installing it with pg_stat_statements and querying it something like below will give stats per query: rpopdb01d/postgres R # SELECT rolname, queryid, round(total_time::numeric, 2) AS total_time, calls, pg_size_pretty(shared_blks_hit*8192) AS shared_hit, pg_size_pretty(int8larger(0, (shared_blks_read*8192 - reads))) AS page_cache_hit, pg_size_pretty(reads) AS physical_read, round(blk_read_time::numeric, 2) AS blk_read_time, round(user_time::numeric, 2) AS user_time, round(system_time::numeric, 2) AS system_time FROM pg_stat_statements s JOIN pg_stat_kcache() k USING (userid, dbid, queryid) JOIN pg_database d ON s.dbid = d.oid JOIN pg_roles r ON r.oid = userid WHERE datname != 'postgres' AND datname NOT LIKE 'template%' ORDER BY reads DESC LIMIT 1; rolname | queryid | total_time | calls | shared_hit | page_cache_hit | physical_read | blk_read_time | user_time | system_time ---------+------------+--------------+----------+------------+----------------+---------------+---------------+-----------+------------- rpop | 3183006759 | 309049021.97 | 38098195 | 276 TB | 27 TB | 22 TB | 75485646.81 | 269508.98 | 35635.96 (1 row) Time: 18.605 ms rpopdb01d/postgres R # Query text may be resolved by queryid something like SELECT query FROM pg_stat_statements WHERE queryid = 3183006759. Works only with 9.4+ and gives you statistics per query for all the time, not the current state.
|
