Re: Buffer statistics for pg_stat_statements

From: Takahiro Itagaki <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp>
To: pgsql-hackers(at)postgresql(dot)org, Cedric Villemain <cedric(dot)villemain(at)dalibo(dot)com>
Subject: Re: Buffer statistics for pg_stat_statements
Date: 2009-12-22 08:27:19
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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.

! 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

Takahiro Itagaki
NTT Open Source Software Center

Attachment Content-Type Size
pg_stat_statements_bufusage_20091222.patch application/octet-stream 14.9 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Simon Riggs 2009-12-22 08:34:32 Re: alpha3 release schedule?
Previous Message Fujii Masao 2009-12-22 07:21:06 Re: Streaming replication and non-blocking I/O