Re: Buffer statistics for pg_stat_statements

2009/12/22 Takahiro Itagaki <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp>:
>
> Cedric Villemain <cedric(dot)villemain(at)dalibo(dot)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.

Yes, you are right.

>
> ! 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
>
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>
>