François Beausoleil wrote (on 18/09/2013):
Hi! Is it possible to get the plan of a query that's currently running? I have queries which normally take about 15 minutes, but are now at 2+ hours. I forgot to add the code to dump the plan to STDOUT, so I really don't know. It might be data volume, but I want to confirm that the plan isn't bogus. I'm doing batch imports of data on "PostgreSQL 9.1.9 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit". Thanks, François Beausoleil
Running an EXPLAIN on the exact query should give you the plan. An EXPLAIN ANALYZE would have to wait for the query to complete either way, so you wouldn't be able to get it mid-way through a running process.
If you don't know the exact query running, then if the stats_command_string config setting is on, you should be able to get it by querying the pg_stat_activity view.
The only other problem I can think of is if there are temporary tables or other session-specific objects that would exist only within the running process.
Another possibility to consider (which would also show up in pg_stat_activity or similar views) is that the query is waiting on some kind of lock, rather than just executing slowly.
Regards, -- Rowan Collins [IMSoP] -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
