Query progress indication
Edit: there was a presentation on this in 2013. The source for that is here.
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: