Thread: query progress indicator

[=============>           ] 56% ETA ...

I know there is no such thing in Postgres right now (though there is
pg_stat_activity). But is there database product that can do this?

--
dave

David Garamond wrote:
> [=============>           ] 56% ETA ...
>
> I know there is no such thing in Postgres right now (though there is
> pg_stat_activity). But is there database product that can do this?

Well... you could do something it in PostgreSQL.  If it's the recovery of the data that's causing selects to go for too
longwithout feedback, simply open a cursor and increment the progress bar as you bring result back from the cursor. If
it'sthe query itself, break it up into smaller pieces. 

If there are a bunch of changes to be done, try increasing the number of inserts/updates by making the criteria more
specific,eg: instead of 'where date between '2004-01-01' and '2004-01-31', break it up into one day at a time. Then you
canmeasure progress by the number of updates that have completed. 

For longer or larger change sets, you have to address the issue of knowing how many changes are actually going to be
madewithout actually performing the query - witness the difference between "explain" and "explain analyze" :) 

These are just my ideas for doing things from the application side of things. Perhaps they might be of some use.

Alex Satrapa

On Mon, 9 Feb 2004, David Garamond wrote:

> [=============>           ] 56% ETA ...
>
> I know there is no such thing in Postgres right now (though there is
> pg_stat_activity). But is there database product that can do this?
>

When pg is processing a query it doesn't know how long it will take, so it
can't make an estimate.  Consider a query on a very large table with no
indexes such as "SELECT * FROM tab WHERE col = 1 LIMIT 1;"  As it will be
forced to do a sequential scan of the table there is no way of telling if
a matching row will be found on the first row or never which will take
wildly different amounts of time.

Kris Jurka