Re: pg_stat_statements

Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:

> I think you want to see the distribution of execution times for
> particular queries without needing to log *every* execution, including
> parameters. I think I understand now what you are asking for and why you
> are asking for it.

Yes. In many case, major influential queries don't depend on thier
parameters. Also, averages of execution time are useful if you want to
log indivisual queries that takes long time compared to the averages.
For example, you can use configuration like:
"If an execution time is 3 times longer than the average of the same plan,
write the explain-tree into log".

That reminds me, it might have association with Auto-explain patch.
Is it given up?
http://archives.postgresql.org/pgsql-patches/2008-01/msg00123.php

> We could also have a function that causes each backend to dump the
> current averages of all plans through to the stats collector, so you can
> assemble a global view. But that should be on-demand, not a continuous
> flow of stats, IMHO.

Hmm, it's interesting idea. It doesn't require any additional shared
memory and max length of stat message would be enough for typical
queries (PGSTAT_MSG_PAYLOAD is 1000 bytes or less). I'm not sure
how to *notify* all backends to send plans to collector, but we could
send plans periodically instead. Bloated pgstat.stat file might be
another problem, and I have no idea about it.

I think the stats collector way is not more than the shared memory way in
efficiency, but it is more flexible. I'll reconsider and compare them...

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