Re: Query plan for currently executing query?

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]