On 18/09/2013 14:44, "Rowan Collins" <rowan.collins@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
