Re: Buffer pool statistics in Explain Analyze

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

> Hi,
>
> I believe it makes sense adding some more details to explain analyze output
> like the number of pages read/written. This will allow one to understand the
> workload the query puts on the server making it easier to tune queries,
> choose the best indices, etc.

Hah, funny you should bring this up -- I was just experimenting here with just
such an idea.

The main problem I ran into was that the instrumentation nodes currently are
nested. That is, all the time for your children counts against you as well. Is
that what we want for I/O costs?

If it is then I think it's fairly simple, have a global set of counters for
various I/O events which are zeroed when the executor starts. Every time an
instrumentation node starts it notes the starting point for all those
counters, whenever it ends take the difference and add that to its personal
counts.

If it isn't then things are a bit trickier. We could store a pointer to the
"current" instrumentation node in a global variable but the trick is what to
restore when a node finishes. We could keep a stack of instrumentation nodes
but I wonder if that would really match the executor stack perfectly. I don't
see why not but it seems fragile.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's PostGIS support!