Re: Progress indication prototype

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.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company