Re: EXPLAIN progress info

Tom Lane wrote:
> Gregory Stark <stark@enterprisedb.com> writes:
>> There are downsides:
>
> Insurmountable ones at that.  This one already makes it a non-starter:
>
>> a) the overhead of counting rows and loops is there for every query execution,
>> even if you don't do explain analyze.
>
> and you are also engaging in a flight of fantasy about what the
> client-side code might be able to handle.  Particularly if it's buried
> inside, say, httpd or some huge Java app.  Yeah, you could possibly make
> it work for the case that the problem query was manually executed in
> psql, but that doesn't cover very much real-world territory.

I think there's two different use cases here. The one that Greg's
proposal would be good for is a GUI, like pgAdmin. It would be cool to
see how a query progresses through the EXPLAIN tree when you run it from
the query tool. That would be great for visualizing the executor; a
great teaching tool.

But I agree it's no good for use by a DBA to monitor a live system
running a real-world application. For that we do need something else.

> You'd be far more likely to get somewhere with a design that involves
> looking from another session to see if anything's happening.  In the
> case of queries that are making database changes, pgstattuple is
> certainly a usable option.  For SELECT-only queries, I agree it's
> harder, but it's still possible.  I seem to recall some discussion of
> including a low-overhead progress counter of some kind in the
> pg_stat_activity state exposed by a backend.  The number of rows so far
> processed by execMain.c in the current query might do for the
> definition.

Yeah, something like this would be better for monitoring a live system.

The number of rows processed by execMain.c would only count the number
of rows processed by the top node of the tree, right? For a query that
for example performs a gigantic sort, that would be 0 until the sort is
done, which is not good. It's hard to come up with a single counter
that's representative :-(.

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com