I found a good answer here: Tracking progress of an update statement
The trick is to first create a sequence (name it as you like):
CREATE SEQUENCE query_progress START 1;
Then append to your query’s WHERE part:
AND NEXTVAL('query_progress')!=0
Now you can query the progress:
SELECT NEXTVAL('query_progress');
Finally don’t forget to get rid of the sequence:
DROP SEQUENCE query_progress;
Note that this will most likely make your query run even slower and every time you check progress it will additionally increment the value. The above link suggested creating a temporary sequence but PostgreSQL doesn’t seem to make them visible across sessions.
How to track query progress in PostgreSQL? — Stack Overflow
