Query progress indication

From pgwiki

 WARNING: This page has been migrated to the PostgreSQL Wiki. Please do not edit this page or your changes may be lost!

Postgres currently doesn't give any meaningful feedback about the query execution process to the user. This would be valuable for:

1. reporting whether the execution of a query is blocked on a lock; many users in #postgresql are confused about why their query takes so long to execute, when in fact it is blocked on a lock
2. reporting the progress of a long-running analysis query. When interactively executing complex, long-running queries, providing feedback about how long they will take to execute (and approximate answers in the mean-time) would be cool
3. reporting the progress of long-running utility queries. For example, it can be difficult to predict whether the runtime of a large CREATE INDEX will be minutes or hours; similarly, the progress of manual VACUUMs can be difficult to estimate accurately

This has been discussed in the DBMS literature (see below). Offhand, I think implementing #1 and #3 would be pretty doable (perhaps with FE/BE changes), but #2 would take some Thought.

References

Relevant papers:

• A Lightweight Online Framework For Query Progress Indicators
• Multi-query SQL Progress Indicators
• Toward a Progress Indicator for Database Queries
• Increasing the Accuracy and Coverage of SQL Progress Indicators

Relevant pgsql-hackers threads:

• Progress bar updates