How do I find out how far along my PostgreSQL query is?

Question

I have a pretty decent idea of how many rows my SELECT...INTO query will actually process (e.g. I know how many will materialize).

I understand Postgres won't tell me percentage completeness, is there a way (buried deep in logs, system tables, or otherwise) that I can find out how many rows have been pumped into the destination table or have been read by the SELECT query?

Answer 1

As Daniel Vérité mentioned there doesn't seem to be a generic solution. When loading data into a table from a file the following technique can be used to get the progress of the load.

COPY command console progress bar

Create an empty table.

CREATE TABLE mytest (n int);

Create a data file with 10 million lines for loading into the table.

$ seq 10000000 > /tmp/data.txt

Load data from file into the table and display a progress bar.

$ pv /tmp/data.txt | psql -c "COPY mytest FROM STDIN;"

Demo

How this works

By using the copy commands STDIN option we can feed in the data for the copy operation from another process. The pv command will output a file and track it's progress displaying a progress bar, ETA, total time elapsed and the rate of data transfer.

COPY command graphical progress bar

Using the same general technique we could display a progress bar in a graphical application or a web-based application. Using python for example the psycopg2 module lets you call the copy command with a file object of your choosing. You could then track how much of your file object has been read and display a progress bar.

Answer 2

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.

Answer 3

Until the progress report functionality won't be extended, as @AmirAliAkbari mentioned in his answer, here is an OS-level workaround.

This works only on Linuxes, but probably there are easily googlable similar solutions for any operating systems.

The largest advantage and also disadvantage of the PostgreSQL, that all of its backends are simple single-threaded processes, using lseek(), read() and write() to manipulate their table files, while they are interacting on shared mem and locks.

This results, all of its backend processes are working always on a single query, which can be easily found, and easily straced.

First, you can see the backend PID from a SELECT * FROM pg_stat_activity;:

29805270 | dbname  | 20019 |    16384 | username  |                  |             |                 |          -1 | 2018-09-19 21:31:57.68234+02  | 2018-09-19 21:31:59.435376+02 | 2018-09-\
20 00:34:30.892382+02 | 2018-09-20 00:34:30.892386+02 | Client          | ClientRead | active              |       92778 |        92778 |  INSERT INTO ...something...

The third column is the pid. In PostgreSQL, it is the same as the Linux process pid of the backend.

Next, you can strace it, for example by a strace -p 20019 -s 8192: (-s 8192 is useful because postgresql works with 8192 byte long blocks).

sendto(10, "C\0\0\0\17INSERT 0 1\0Z\0\0\0\5T", 22, 0, NULL, 0) = 22
recvfrom(10, "Q\0\0\1\267 INSERT <removed by @peterh>", 8192, 0, NULL, NULL) = 440
sendto(10, "C\0\0\0\17INSERT 0 1\0Z\0\0\0\5T", 22, 0, NULL, 0) = 22
lseek(298, 343634345)...
read(298, "<block data which was read in>"....
write(298, "<block data which was written out>"...

The meanings:

• sendto happens if the backend answers something to a client. In the example, it answers the result of an INSERT query.
• recvfrom happens if the backend gets something from a client. It is typically a new query, in the example, yet another INSERT.
• lseek happens if the backend switches position in a table file.
• read happens if the backend reads a block in from a table file.
• write happens if the backend writes a block out into a table file.

In the case of read and write, you can also see the content of that block in the table. It can help a lot to understand, what is it doing and where is it.

In the case of recvfrom, you can see the actual query what the backend has got.

Answer 4

As said in other answers, currently there is no direct way for progress reporting in general.

PostgreSQL has the ability to report the progress of certain commands during command execution. Currently, the only command which supports progress reporting is VACUUM. This may be expanded in the future.

However, starting from 9.6, whenever VACUUM is running, the pg_stat_progress_vacuum view will contain one row for each backend (including autovacuum worker processes) that is currently vacuuming. Further details about pg_stat_progress_vacuum can be found in documentation: 27.4 Progress Reporting.