Re: contrib/pg_stat_statements 1226 - Mailing list pgsql-hackers

ITAGAKI Takahiro <itagaki.takahiro@oss.ntt.co.jp> writes:
> Here is an updated version of contrib/pg_stat_statements patch.

I've committed this with significant revisions.  Other than the points
already mentioned in previous messages:

* I removed the proposed changes to the behavior of the core EXPLAIN
code.  I think that that should be submitted and discussed as a separate
patch, not slide in under the misleading title of being a "contrib
module".  I'm personally against those changes anyway, on two grounds:

1. The proposed change to track system/user CPU time presents an
enormous cost, and no argument has been made to show that there is any
comparable benefit.  The change causes each EXPLAIN ANALYZE tracking
call to invoke getrusage() as well as gettimeofday().  I did a little
bit of testing and found that this is over seven times slower on Fedora
9 on x86_64 (Xeon hardware) and over twenty-seven times slower on Darwin
(on Core 2 Duo hardware).  Considering that EXPLAIN ANALYZE overhead is
already higher than anyone would like, you would need a pretty darn
convincing argument to persuade us to accept that kind of slowdown.
At the very least the code would need to be modified so that it doesn't
execute getrusage() unless the user is actually going to look at the
results.

2. I'm unconvinced by the proposed changes to accumulate backend-local
I/O counters, too.  The fact of the matter is that those counters are
left over from Berkeley days, a time when PG hackers tended to do their
performance measurements in standalone backends (!).  They're obviously
not the full story now on write measurements, and I don't have any
confidence in them as read measurements either, particularly seeing that
the wave of the future is likely to be asynchronous read operations
(with the posix_fadvise patch and foreseeable follow-on work).  I think
those counters should more likely be done away with than
institutionalized in EXPLAIN ANALYZE output.  You can get more reliable
information about what's happening from the existing pgstats system-wide
I/O counts.

* I changed the default track setting to "top".  I don't see the
likelihood that someone would load this module into their server
and not want it turned on.

* I'm not entirely seeing the point of a server-wide tracking facility
that only counts SELECT/INSERT/UPDATE/DELETE.  ISTM this should be
modified to count utility commands too; which probably means adding some
hooks around ProcessUtility (and what about autovacuum?).  I left that
work for someone else to do, though.

* As already mentioned I find the entry_dealloc logic pretty darn
dubious; but I didn't touch that either in this go-round.  If we do
keep it in its current form, ISTM that usage ought to be proportional
to total execution time not total call count.
        regards, tom lane