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(at)mit(dot)edu> wrote:
>> On Tue, Aug 17, 2010 at 11:29 PM, Dave Page <dpage(at)pgadmin(dot)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 the end 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 as execution time). If I could get the explain analyze results *and* the actual query results, that would be a huge win, too.

Cheers,
M