Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
I also want to know. –  francs Sep 27 '13 at 7:13
add comment

1 Answer

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.