Re: On-demand running query plans using auto_explain and signals

Hi,

On 12/01/2015 10:34 AM, Shulgin, Oleksandr wrote:

I have the plans to make something from this on top of
pg_stat_statements and auto_explain, as I've mentioned last time.  The
next iteration will be based on the two latest patches above, so it
still makes sense to review them.

As for EXPLAIN ANALYZE support, it will require changes to core, but
this can be done separately.

I'm re-reading the thread, and I have to admit I'm utterly confused what is the current plan, what features it's supposed to provide and whether it will solve the use case I'm most interested in. Oleksandr, could you please post a summary explaining that?

My use case for this functionality is debugging of long-running queries, particularly getting EXPLAIN ANALYZE for them. In such cases I either can't run the EXPLAIN ANALYZE manually because it will either run forever (just like the query) and may not be the same (e.g. due to recent ANALYZE). So we need to extract the data from the process executing the query.

I'm not essentially opposed to doing this in an extension (better an extension than nothing), but I don't quite see how you could to do that from pg_stat_statements or auto_explain. AFAIK both extensions merely use hooks before/after the executor, and therefore can't do anything in between (while the query is actually running).

Perhaps you don't intend to solve this particular use case? Which use cases are you aiming to solve, then? Could you explain?

Maybe all we need to do is add another hook somewhere in the executor, and push the explain analyze into pg_stat_statements once in a while, entirely eliminating the need for inter-process communication (signals, queues, ...).

I'm pretty sure we don't need this for "short" queries, because in those cases we have other means to get the explain analyze (e.g. running the query again or auto_explain). So I can imagine having a rather high threshold (say, 60 seconds or even more), and we'd only push the explain analyze after crossing it. And then we'd only update it once in a while - say, again every 60 seconds.

Of course, this might be configurable by two GUCs:

   pg_stat_statements.explain_analyze_threshold = 60  # -1 is "off"
   pg_stat_statements.explain_analyze_refresh = 60

FWIW I'd still prefer having "EXPLAIN ANALYZE" in core, but better this than nothing.