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:

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 -- Isn't this easily gleaned from the "waiting" boolean in pg_stat_activity?
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.

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:

• Add a generic command progress reporting facility.

And the following uses it:

• Add simple VACUUM progress reporting.

Docs for the same:

• 27.4. Progress Reporting

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