Re: Progress indication prototype

On Aug 18, 2010, at 9:02 AM, Robert Haas wrote:

> On Wed, Aug 18, 2010 at 8:45 AM, Greg Stark <stark@mit.edu> wrote:
>> On Tue, Aug 17, 2010 at 11:29 PM, Dave Page <dpage@pgadmin.org> wrote:
>>> Which is ideal for monitoring your own connection - having the info in
>>> the pg_stat_activity is also valuable for monitoring and system
>>> administration. Both would be ideal :-)
>>
>> Hm, I think I've come around to the idea that having the info in
>> pg_stat_activity would be very nice. I can just picture sitting in
>> pgadmin while a bunch of reports are running and seeing progress bars
>> for all of them...
>>
>> But progress bars alone aren't really the big prize. I would really
>> love to see the explain plans for running queries. This would improve
>> the DBAs view of what's going on in the system immensely. Currently
>> you have to grab the query and try to set up a similar environment for
>> it to run explain on it. If analyze has run since or if the tables
>> have grown or shrank or if the query was run with some constants as
>> parameters it can be awkward. If some of the tables in the query were
>> temporary tables it can be impossible. You can never really be sure
>> you're looking at precisely the same plan than the other user's
>> session is running.
>>
>> But stuffing the whole json or xml explain plan into pg_stat_activity
>> seems like it doesn't really fit the same model that the existing
>> infrastructure is designed around. It could be quite large and if we
>> want to support progress feedback it could change quite frequently.
>>
>> We do stuff the whole query there (up to a limited size) so maybe I'm
>> all wet and stuffing the explain plan in there would be fine?
>
> It seems to me that progress reporting could add quite a bit of
> overhead.  For example, in the whole-database vacuum case, the most
> logical way to report progress would be to compute pages visited
> divided by pages to be visited.  But the total number of pages to be
> visited is something that doesn't need to be computed in advance
> unless someone cares about progress.  I don't think we want to incur
> that overhead in all cases just on the off chance someone might ask.
> We need to think about ways to structure this so that it only costs
> when someone's using it.

I wish that I could get explain analyze output step-by-step while running a long query instead of seeing it jump out at
theend of execution. Some queries "never" end and it would be nice to see which step is spinning (explain can be a red
herring).To me the "progress bar" is nice, but I don't see how it would be reliable enough to draw any inferences (such
asexecution time). If I could get the explain analyze results *and* the actual query results, that would be a huge win,
too.

Cheers,
M