Query progress indication

From PostgreSQL wiki
Jump to navigationJump to search

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:

And the following uses it:

Docs for the same:

References

Relevant papers:

Relevant pgsql-hackers threads: