Re: Improvement of pg_stat_statement usage about buffer hit ratio

(2013/11/19 11:12), KONDO Mitsumasa wrote:
> (2013/11/19 3:56), Peter Geoghegan wrote:
>> On Mon, Nov 18, 2013 at 10:49 AM, Fujii Masao <masao.fujii@gmail.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,
Oh, I forgot to say it...
I beleive that essence of information technology is to show more informative 
information in little information with low cost. If it wrong, please let me know 
the reason.

Regards,
--
Mitsumasa KONDO
NTT Open Source Software Center