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

2015-12-24 3:23 GMT+01:00 Michael Paquier <michael(dot)paquier(at)gmail(dot)com>:

> On Thu, Dec 17, 2015 at 6:45 PM, Shulgin, Oleksandr
> <oleksandr(dot)shulgin(at)zalando(dot)de> wrote:
> > On Wed, Dec 16, 2015 at 8:39 PM, Tomas Vondra <
> tomas(dot)vondra(at)2ndquadrant(dot)com>
> > wrote:
> >> 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?
> >
> > Thanks for your interest in this patch!
> >
> > My motivation is the same as your use case: having a long-running query,
> be
> > able to look inside this exact query run by this exact backend.
> >
> > I admit the evolution of ideas in this patch can be very confusing: we
> were
> > trying a number of different approaches, w/o thinking deeply on the
> > implications, just to have a proof of concept.
>
> It's great to see ideas of concepts and things to help address those
> issues, at least we are agreeing that there is a hole in the
> instrumentation and that it would be nice to fill it with something.
> Still, it is not completely clear which approach would be taken. Is it
> fair to mark the current patch as returned with feedback then?
>

+1

Pavel

> --
> Michael
>