Here is a proposal to add buffer usage information to EXPLAIN and contrib/pg_stat_statements. We can retrieve new values 'gets', 'reads' and 'temp':
- gets : total number of buffer pool access - reads : total number of data file access - temp : total number of temp file access (sort) In EXPLAIN, we can use "EXPLAIN (ANALYZE, BUFFER) ..." syntax. Each executor node shows buffer usage only in it; parent nodes don't contain buffer usages in their sub nodes. In pg_stat_statements, new 3 columns are added to the view. We can determine queries that consume I/O bandwidth using "SELECT * FROM pg_stat_statements ORDER BY reads DESC". We will find out bad queries easily using those buffer and disk access information in addition to duration statistics. I implementd this feature using an instrumentation stack. A global variable CurrentInstrument points top of the stack, and each Instrumentation are linked with newly added 'prev' field. The stack must be reset even on error because each innstrumentation might have been deallocated already. I added codes to reset stack in main loop of backend for the purpose. TopInstrument is a special node that sums up all of the child nodes. It tracks QueryDesc.totaltime and used in pg_stat_statements. There might be another idea that walking around on planstate tree and gathering all counters in the module, but very complex codes are needed. I chose a simple way. I'll write documentations if this design is accepted. Comments welcome. Output samples are below: [EXPLAIN] =# EXPLAIN (ANALYZE, BUFFER) SELECT * FROM pgbench_accounts ORDER BY bid; QUERY PLAN ---------------------------------------------------------- Sort (cost=...) (actual ...) (gets=0 reads=0 temp=1309) Sort Key: bid Sort Method: external sort Disk: 10472kB -> Seq Scan on pgbench_accounts (cost=...) (actual ...) (gets=1798 reads=1644 temp=0) Total runtime: 75.867 ms [contrib/pg_stat_statements] =# SELECT query, gets, reads FROM pg_stat_statements ORDER BY gets DESC LIMIT 4; query | gets | reads ----------------------------------------------------------------------+-------+------- UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2; | 58628 | 1 UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2; | 26999 | 1929 UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2; | 25474 | 1 SELECT abalance FROM pgbench_accounts WHERE aid = $1; | 19950 | 0 (4 rows) Regards, --- ITAGAKI Takahiro NTT Open Source Software Center
buffer_usage-20090817.patch
Description: Binary data
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers