query progress indicator

[David Garamond]

[=============> ] 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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Alex Satrapa]

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 long without feedback, simply open a cursor and increment the progress bar as you bring result back from the cursor. If it's the 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 can measure 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 made without 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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Kris Jurka]

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
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match