Query progress indication
Postgres currently doesn't give any meaningful feedback about the query execution process to the user. This would be valuable for:
- 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 -- Isn't this easily gleaned from the "waiting" boolean in pg_stat_activity?
- 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
- 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.
Oracle already has something similar. Any operation that takes longer than 6 seconds is automatically tracked in V$SESSION_LONGOPS. The tracking is done on e.g. the step level of the execution plan ("TABLE SCAN", "12344 ov 654764 blocks read").
In PostgreSQL 9.6, a generic facility for (3) above has been introduced:
And the following uses it:
Docs for the same:
- 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: