> > 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.
I am not sure if this impact (in terms of compatibility) is really that big. As far as I could understand, pgAdmin parses modified explain analyze output well. However, pgAdmin does not support "verbose".
The impact in terms of "computational" overhead could be measured. I guess it depends only on the number of calls to the instrumentation (that is either rows or loops). Thus, after explaining some basic select from generate_series, one could adjust the timings. We could even incorporate this to EXPLAIN ANALYZE, so it would display "cpu is 0.5s, while instrumentation cpu is 0.3s".
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.
Single number per query is sufficient only for pg_stat_statements purposes. That will give an insight of what the top consuming queries are (by cpu time, by gets, etc).
However, single "gets=... reads=..." is not sufficient to pinpoint the problem especially in case of complex query (that is comparable to "query returned N rows" vs "this plan node returned N rows") .
=# 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 pgAdmin (or whatever client) had an option to fetch that counters for each and every SQL query and display the consumed resources at a separate tab. I mean, even before/after plain "select" (without any explain). That will show you how the query would behave without any instrumentation.
Regards,
Vladimir Sitnikov