public inbox for [email protected]  

help / color / mirror / Atom feed

From: Lukas Fittl <[email protected]>
To: Robert Haas <[email protected]>
Cc: Matheus Alcantara <[email protected]>
Cc: [email protected]
Cc: Tom Lane <[email protected]>
Subject: Re: Add custom EXPLAIN options support to auto_explain
Date: Thu, 2 Apr 2026 19:32:36 -0700
Message-ID: <CAP53PkyQS_aSWw_PouYj-zr5A_JUeKD0sCdKTkWpk0uFQMBcyw@mail.gmail.com> (raw)
In-Reply-To: <CA+TgmoYUdeCdGfk8H6Ni2obXVixLvYaDkRGtxKLEmaCVNffsVA@mail.gmail.com>
References: <[email protected]>
	<CA+Tgmob-0W8306mvrJX5Urtqt1AAasu8pi4yLrZ1XfwZU-Uj1w@mail.gmail.com>
	<CA+TgmoYUdeCdGfk8H6Ni2obXVixLvYaDkRGtxKLEmaCVNffsVA@mail.gmail.com>

On Tue, Mar 31, 2026 at 9:19 AM Robert Haas <[email protected]> wrote:
>
> On Mon, Mar 30, 2026 at 5:49 PM Robert Haas <[email protected]> wrote:
> > I'm currently poking at some ideas for fixing this... more soon.
>
> Here are some patches. I got started poking at this in earnest
> because, on the pg_plan_advice thread, Lukas was saying that instead
> of adopting pg_collect_advice, we should just add an option to send
> advice strings for each executed query to the server log. I went to
> implement that and then felt like it should really be part of
> auto_explain rather than its own thing, which took me down a bit of a
> rathole. But I eventually found my way back out of it, so here's a
> patch set implementing auto_explain.log_extension_options.

Thanks for the effort, I think this is a good approach to solve the
immediate need for plan advice (to have a facility to capture the
advice strings for a set of queries), whilst avoiding introducing a
new module, or completely new log settings.

FWIW, initially I wasn't sure about this approach, since I typically
see auto_explain focused on capturing outliers, so it wouldn't
necessarily help you to capture the "good plan" (since that won't be
an outlier). But since a superuser can modify auto_explain on a
per-session basis this is similar to having a dedicated log setting,
and it seems reasonable to not just have the advice string but also
the plan that it is associated with. Its also useful that auto_explain
has a sample rate option, so one could sample 1% of all queries for a
few minutes to get a sense for the workload and the associated plan
advice strings.

And, just as a data point on why this is more generally useful besides
pg_plan_advice and pg_overexplain: I've been thinking of utilizing the
custom EXPLAIN option mechanism to log Plan ID values in EXPLAIN for
an extension I maintain (pg_stat_plans), and this would allow that
extension to also log the Plan IDs in auto_explain output, which would
be very useful.

> Anyway, if you apply all these patches it does solve the problem that
> pg_collect_advice was targeting, modulo the need for some log parsing.
> You can do this:
>
> pg_plan_advice.always_store_advice_details = on
> auto_explain.log_min_duration = 0
> auto_explain.log_extension_options = 'plan_advice'
>
> And then you get log output like this:
>
> 2026-03-31 12:16:18.784 EDT [75224] LOG:  duration: 0.013 ms  plan:
> Query Text: select 1;
> Result  (cost=0.00..0.01 rows=1 width=4)
> Generated Plan Advice:
>   NO_GATHER("*RESULT*")

Looks good, that makes sense to me in terms of user experience to
target for this release.

I have only skimmed the code before running out of energy for today,
but will do a closer code review tomorrow.

Thanks,
Lukas

-- 
Lukas Fittl

view thread (16+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Add custom EXPLAIN options support to auto_explain
  In-Reply-To: <CAP53PkyQS_aSWw_PouYj-zr5A_JUeKD0sCdKTkWpk0uFQMBcyw@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox