I had a small play with this. Pretty cool to be able to track progress
for COPY and VACUUM jobs. For some reason I could never elicit any
numbers (other than the default Nan) for a query (tried 'SELECT count(*)
FROM pgbench_accounts' but figured aggregates probably don't qualify as
simple, and also 'SELECT * FROM pgbench_accounts').
I'm thinking that complex queries is precisely where people would want
to see this sort of indicator - but maybe have a read of:
http://research.microsoft.com/pubs/76171/progress.pdf
This paper seems to suggest that there are real classes of query where a
useful progress indicator is going to be extremely hard to construct.
However it may still be a useful feature to have for all those other
queries!
Also I'm inclined to agree with Robert and think that a more accurate,
more performance obtrusive but settable on demand implementation is the
way to go.
Cheers
Mark
On 17/08/10 17:19, Peter Eisentraut wrote:
> Here is a small prototype for a query progress indicator.
>
> Past discussions seemed to indicate that the best place to report this
> would be in pg_stat_activity. So that's what this does. You can try it
> out with any of the following (on a sufficiently large table): VACUUM
> (lazy) (also autovacuum), COPY out from table, COPY in from file,
> table-rewriting ALTER TABLE (e.g., add column with default), or a very
> simple query. Run the command, and stare at pg_stat_activity (perhaps
> via "watch") in a separate session.
>
> More can be added, and the exact placement of the counters is debatable,
> but those might be details to be worked out later. Note, my emphasis
> here is on maintenance commands; I don't plan to do a progress
> estimation of complex queries at this time.
>
> Some thoughts:
>
> - Are the interfaces OK?
>
> - Is this going to be too slow to be useful?
>
> - Should there be a separate switch to turn it on (currently
> track_activities)?
>
> - How to handle commands that process multiple tables? For example,
> lazy VACUUM on a single table is pretty easy to track (count the block
> numbers), but what about a database-wide lazy VACUUM?
>
>