0

I have a query running in Postgres 9.3 for more than 24h now (namely an EXPLAIN ANALYZE VERBOSE, linked to another question).

I check the "Server status" in pgAdmin which shows the query "Active". I ran some standard lock detection but it didn't return anything.

I'm not sure why it's taking so long: is it waiting for a lock that it can never get (no point in waiting)? Is it just slow (let it run longer)?

In case it's relevant, the database itself is quite big (500 million rows on one of the foreign table, several millions on others) and the data directory uses 240 out of the 340 GB of the disk, so has ~100 GB left. It is a Windows 2012 Server with 12 GB RAM, 6 GB left.

EDIT: The query itself finally finished after 28h30 (because of recursive-like delete), but the question is still valid: what can I check to make sure I'm not waiting in vain? (removed postgresql-9.3 because it would be better to have a more general answer)

Improve this question
2
  • 2
    What does the column wait_event_type show you? You could also use the function pg_blocking_pids() to get a list of other transactions that are blocking your query (in case it's blocked) Commented Aug 31, 2018 at 13:54
  • @a_horse_with_no_name in select * from pg_stat_activity? I think it was introduced in 9.6 and I'm running 9.3... I ran the query again (it finished it that case, after 28h30) and the only interesting column I could see is waiting=false. Commented Aug 31, 2018 at 17:58

1 Answer 1

Reset to default
2

That your query on the lock table didn't return anything provided the answer to your question--it was not waiting on a lock. (Assuming you did it correctly).

It is still kind of annoying and error-prone to have to open a new session and run the query and interpret the results, so I wrote a patch to send NOTICE when you are waiting on a lock. There were several proposed ways to improve it, but no consensus on the best way, so for now it is just a proposal. If you are willing to compile your own PostgreSQL you can always adopt this patch for your use. I don't know what pgAdmin will do with such notifications, however, as I use psql not pgAdmin as my main tool.

As @a_horse_with_no_name indicates, you can also use columns in pg_stat_activity to get information on this. The quality of that information improves from version to version. Running older versions does come at a price. Putting in the effort to upgrade every couple years is well worth it.

Finally, your system monitoring tools are invaluable. A quick look at "top" (for Linux) or "Task Manager" (for Windows) would probably have immediately indicated that your job was busy doing something--either CPU or disk.

Improve this answer
1
  • The Task Manager is indeed a good idea. The database is otherwise busy with other queries, but I could filter on PID/IP address. Commented Sep 2, 2018 at 9:51

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.