Re: Buffer statistics for pg_stat_statements

Cedric Villemain <cedric.villemain@dalibo.com> wrote:

> Le vendredi 18 decembre 2009 09:44:40, Takahiro Itagaki a ecrit :
> > I'd like to add per-query buffer usage into contrib/pg_stat_statements.

Here is a patch to add buffer usage columns into pg_stat_statements.
It also changes initialzation of the result TupleDesc from manually
coded routines to get_call_result_type().

> Perhaps it can be usefull to have the percentage for hit/read ratio computed
> in the view ?

I think different DBAs want different derived values; Some of them might want
the buffer hit ratio, but others might request numbers per query. I'd like to
privide only raw values from pg_stat_statements to keep it simple, but I added
a computational expression of hit percentage in the documentation.

! bench=# SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit /
!                nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
!           FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;
! -[ RECORD 1 ]---------------------------------------------------------------------
! query       | UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2;
! calls       | 3000
! total_time  | 9.60900100000002
! rows        | 2836
! hit_percent | 99.9778970000200936

Regards,
---
Takahiro Itagaki
NTT Open Source Software Center