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.

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services