There does not seem to be a generic, supported method, but there are some tricks that may be used in limited contexts to evaluate the progress of an individual query. Here are some of them.
Sequences
When a SELECT or UPDATE query includes any nextval(sequence_name)
, or an INSERT has a destination column with a nextval
as default, the current sequence value can be repeatedly queried in another session with SELECT sequence_name.last_value
. It works because sequences are not bounded by transactions. When the execution plan is such that the sequence is incremented linearly during the query, it can be used as a progress indicator.
pgstattuple
The pgstattuple contrib module provides functions that can peek directly at the data pages. It appears that when tuples are inserted into an empty table and not yet committed, they are counted in the dead_tuple_count
field from the pgstattuple
function.
Demo with 9.1: create an empty table
CREATE TABLE tt AS (n numeric);
Let's insert 10M rows into it:
INSERT INTO tt SELECT * FROM random() from generate_series(1,10000000);
In another session, check pgstattuple every second during the insert:
$ while true;
do psql -Atc "select dead_tuple_count from pgstattuple('tt')";
sleep 1;
done
Results:
0
69005
520035
1013430
1492210
1990415
2224625
2772040
3314460
3928660
4317345
4743770
5379430
6080950
6522915
7190395
7953705
8747725
9242045
0
It falls back to 0 when the insert is finished (all the tuples become visible and live).
This trick may also be used when the table is not freshly created, but the initial dead_tuple_count
is likely to have a non-zero value and it may also change concurrently if other write activity such as autovacuum is going on (presumably? Not sure what level of concurrency to expect with autovacuum).
However it can't be used if the table is created by the statement itself (CREATE TABLE ... AS SELECT
or SELECT * INTO newtable
), since the creation is transactioned. The workaround would be to create the table with no rows (add LIMIT 0
) and populate it in the next transaction.
Note that pgstattuple
doesn't come free: it scans the entire table at every call. Also it's limited to superusers.
Custom counter
In Pavel Stehule's blog, he provides a counter function implemented in C that raises NOTICEs at specified numbers of executions. You have to combine the function with the query somehow to let the executor call it. Notices are sent during the query and they don't need a separate session, only a SQL client that displays them (psql
being the obvious candidate).
Example of INSERT INTO reworked to raise notices:
/* transformation */
INSERT INTO destination_table
SELECT (r).*
FROM (SELECT counter(to_destination_table(_source), 1000, true) r
FROM source _source) x
Related question on stackoverflow, for functions:
How to report progress from long-running PostgreSQL function to client
Future options?
As of May 2017, there is a promising patch submitted to the developers community:
[PATCH v2] Progress command to monitor progression of long running SQL queries
which might end up as a generic solution in PostgreSQL 11 or later.
Users who feel like participating in work-in-progress features might
apply the latest version of the patch and try the proposed PROGRESS
command.