Re: contrib/pg_stat_statements 1202

"Vladimir Sitnikov" <sitnikov(dot)vladimir(at)gmail(dot)com> wrote:

> > 2. EXPLAIN ANALYZE VERBOSE shows buffer statistics in 'actual' section.
> >
> I do not get the point of "VERBOSE".
> As far as I understand, explain analyze (without verbose) will anyway add
> overhead for calculation of "gets/hits/cpu". Why discard that information in
> "non verbose" mode? Just to make the investigation more complex?

I thought that output of new counters are too wide and it brakes
compatibility of EXPLAIN ANALYZE. On the other hand, we don't have to
think of compatibility in EXPLAIN ANALYZE VERBOSE because it is newly
added in 8.4. However, overheads should be avoided. We could have
two kinds of instrumentations, time-only or all-stats.

Another idea is to have buffer and cpu counters not in Instrumentation
but in QueryDesc (i.e, only one per query). A new field for the counter
will be initialized in pg_stat_statements module. EXPLAIN ANALYZE could
also use the field but the result is printed only the end of query plan,
as the follows. We can avoid too-wide-line problem with the approach.

=# EXPLAIN ANALYZE SELECT ...;
QUERY PLAN
---------------------------
...
Total runtime: 24.273 ms
CPU usage: user=20.2 sys=2.4 #
Buffer usage: gets=100 reads=10 writes=20 # added lines
Tempfile i/o: reads=50 writes=50 #
(10 rows)

> I wish there was a way to get the results of explain into some table. I wish
> it was the default output "format". That would make life of pgAdmin easier,
> and improve readability even in psql. Do not you think there is something
> wrong with having "cost=... rows=... loops=..." in each and every row?

I absolutely agree that XML-explain and TABLE-expalin are useful,
but they are independent features from pg_stat_statements. So I'd
like to discuss them in separated threads (and it will come in 8.5).

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