Re: Query plan for currently executing query?

On 18/09/2013 14:44, "Rowan Collins" <rowan(dot)collins(at)gmail(dot)com> wrote:

>
>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.

I think the OP was hoping for a solution that would allow him to retrieve
the query plan that was generated at execution time. I've certainly
wished for this type of facility in the past, in the scenario where a
given table may have been ANALYZE in the interim - making it difficult to
perform any useful diagnostics on the problem query.

In cases such as that, it is sometimes better to cancel the currently
running query and restart it in order to take advantage of the newer
statistics and generate a better QEP.

What might be a useful feature addition, is if at the time of the ANALYZE
operation - postgres could identify those currently running queries and
flag them as having begun execution with a dirty/stale QEP. Possibly this
could be extended to heuristically determine if a query might return
faster if it were cancelled and re-executed under a new QEP?

Tim