Re: Progress indication prototype

On Tue, Aug 17, 2010 at 2:19 PM, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> Here is a small prototype for a query progress indicator.

I read and tested the patch. Here are comments to the code itself.

- Oid of pg_stat_get_backend_progress() must be changed because we are using
the id for another function.
- One complier warning:
copy.c:1702: warning: ‘file_size’ may be used uninitialized in this function
- We can move the division "work_done/work_total" to outside of
st_changecount++ block.

> Past discussions seemed to indicate that the best place to report this
> would be in pg_stat_activity.

Agreed. BTW, "query_progress" column shows NaN if progress
counter is unavailable, but NULL would be better.

> VACUUM (lazy) (also autovacuum), table-rewriting ALTER TABLE
We could also support VACUUM FULL, CLUSTER, CREATE INDEX and REINDEX.

> COPY out from table, COPY in from file,
COPY FROM STDIN shows Infinity, but NULL might be better, too.

> a very simple query.
SELECT * FROM tbl;
can report reasonable progress, but
SELECT count(*) FROM tbl;
cannot, because planned_tuple_count of the aggregation is 1.
I hope better solutions for the grouping case because they are used
in complex queries, where the progress counter is eagerly wanted.

> - Are the interfaces OK?

I like the new column in pg_stat_activity to "pull" the progress.
In addition, as previously discussed, we could also have "push"
notifications; Ex. GUC parameter "notice_per_progress" (0.0-1.0),
or periodical NOTIFY messages.

> - Is this going to be too slow to be useful?
> - Should there be a separate switch to turn it on (currently
> track_activities)?

I think we can always track the counters because shared memory
based counters are lightweight enough.

> - 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?

Not only a database-wide lazy VACUUM but also some of maintenance
commands have non-linear progress -- Progress of index scans in VACUUM
is not linear. ALTER TABLE could have REINDEX after table rewrites.

We might need to have arbitrary knowledges for the non-uniform commands;
For example, "CREATE INDEX assigns 75% of the progress for table scan,
and 25% for the final merging of tapes".

--
Itagaki Takahiro