Re: Buffer usage in EXPLAIN and pg_stat_statements - Mailing list pgsql-hackers

2009/8/17 Itagaki Takahiro <itagaki.takahiro@oss.ntt.co.jp>:
> 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)
>

It's should be nice

Pavel

> Regards,
> ---
> ITAGAKI Takahiro
> NTT Open Source Software Center
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>
>