Subject: Re: On-demand running query plans using auto_explain and
 signals
To: "Shulgin, Oleksandr" <oleksandr.shulgin@zalando.de>,
 Simon Riggs <simon@2ndquadrant.com>
References: 
 <CACACo5Sz7G0MFauC082iM=XX_hQ7qQ5ndR4JPo+H-O5vp6iCcQ@mail.gmail.com>
 <CACACo5Q=nEEzAUE3o29WTU0wW7wourZB+SDhwPYzGZPyggF4hg@mail.gmail.com>
 <CAFj8pRAENjXoLQp+_nRZXuqzn7kJRx7YUScD6O=yczmi1CU3qQ@mail.gmail.com>
 <CACACo5QSG0yy9dD_Ots8Zy+djvSOrfwfmU-wC+y8HPgo4tzoqg@mail.gmail.com>
 <CAFj8pRBayKwzi1oiyW1PXfyZeb_HdKRmuHn909-7i7Ao1zXBdw@mail.gmail.com>
 <CA+TgmoYax0YVp5RGpAStyfHanNbRcTWXrOzci34+dbpJTvkCvw@mail.gmail.com>
 <CACACo5StM7ikEEJi64B49k_zpUMXWVhdDKKyiHBS5tv+5S4VfA@mail.gmail.com>
 <CANP8+jJQA4OYCMu-FQeV-zXPFGQN0CUZBXq+cVEC_Pm32vw-1g@mail.gmail.com>
 <CACACo5ReQKEzKyGCsEMYr9v23qsP=tStaFsstTK8G7_vXsWbxA@mail.gmail.com>
 <CACACo5R8J=+btDzS4+KQsAE5QGiZFhushdJL8BPc_63nrySyLQ@mail.gmail.com>
 <32603.1444916567@sss.pgh.pa.us>
 <CAFj8pRBhdv6N4yRfJBd-JjYnfM89c4nshJEDyzxGVTWOCwy6vw@mail.gmail.com>
 <565CBF3C.7080106@dalibo.com>
 <CANP8+j+UCZTH3tnpMOqXjTgXVAaWQ3JwqTEhaFgBvH9q4AMtwA@mail.gmail.com>
 <CACACo5RFc1_meHT6SjiO=5S4yFVSeMhURMorjpkq5khDNPir6w@mail.gmail.com>
Cc: Julien Rouhaud <julien.rouhaud@dalibo.com>,
 Pavel Stehule <pavel.stehule@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>,
 Robert Haas <robertmhaas@gmail.com>,
 PostgreSQL Hackers <pgsql-hackers@postgresql.org>
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Message-ID: <5671BE0E.3040200@2ndquadrant.com>
Date: Wed, 16 Dec 2015 20:39:58 +0100
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:38.0) Gecko/20100101
 Thunderbird/38.1.0
MIME-Version: 1.0
In-Reply-To: 
 <CACACo5RFc1_meHT6SjiO=5S4yFVSeMhURMorjpkq5khDNPir6w@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers