Re: Improvement of pg_stat_statement usage about buffer hit ratio

(2013/11/19 3:56), Peter Geoghegan wrote:
> On Mon, Nov 18, 2013 at 10:49 AM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
>> The same idea was proposed before but not committed because
>> Itagaki thought that pg_stat_statements view should report only raw values.
>> Please read the following thread. I have the same feeling with him.
>> Anyway we should listen to more opinions from other people, though.
>> http://www.postgresql.org/message-id/20091222172719.8B86.52131E4D@oss.ntt.co.jp
I confirmed that Itagaki-san and Mr Cerdic disscution. He said that raw values
be just simple. However, were his changes just simple? I cannot understand his
aesthetics sense and also you, too:-(

PG8.4 before:
> 012 CREATE FUNCTION pg_stat_statements(
> 013 OUT userid oid,
> 014 OUT dbid oid,
> 015 OUT query text,
> 016 OUT calls int8,
> 017 OUT total_time float8,
> 018 OUT rows int8
> 019 )

PG9.0 after:
> 012 CREATE FUNCTION pg_stat_statements(
> 013 OUT userid oid,
> 014 OUT dbid oid,
> 015 OUT query text,
> 016 OUT calls int8,
> 017 OUT total_time float8,
> 018 OUT rows int8,
> 019 OUT shared_blks_hit int8,
> 020 OUT shared_blks_read int8,
> 021 OUT shared_blks_written int8,
> 022 OUT local_blks_hit int8,
> 023 OUT local_blks_read int8,
> 024 OUT local_blks_written int8,
> 025 OUT temp_blks_read int8,
> 026 OUT temp_blks_written int8
> 027 )
It's too complicated, and do you know how to tuning PG from information of
local_* and temp_*?
At least, I think that most user cannot tuning from these information, and it
might not be useful information only part of them.

> +1 from me. I think that a higher level tool needs to be built on
> pg_stat_statements to make things easy for those that want a slick,
> pre-packaged solution.
No. It's not for geek tools and people having pre-packaged solution in big
company, but also for common DBA tools.

By the way, MySQL and Oracle database which are very popular have these
statistics. I think that your argument might disturb people who wants to
migration from these database and will accelerate popularity of these database more.

> As I said on the min/max thread, if we're not
> doing enough to help people who would like to build such a tool, we
> should discuss how we can do better.
Could you tell me how to get min/max statistics with low cost?
I'm not sure about detail of your patch in CF, but it seems very high cost.
Repeatedly, I think that if we want to get drastic detail statistics, we have to
create another tools of statistics. Your patch will be these statistics tools.
However, pg_stat_statement sholud be just light weight.

Regards,
--
Mitsumasa KONDO
NTT Open Source Software Center