MIME-Version: 1.0
References: <DHCYFNC0WIF4.3T1M8AUK0DG7T@gmail.com>
 <CA+Tgmob-0W8306mvrJX5Urtqt1AAasu8pi4yLrZ1XfwZU-Uj1w@mail.gmail.com>
 <CA+TgmoYUdeCdGfk8H6Ni2obXVixLvYaDkRGtxKLEmaCVNffsVA@mail.gmail.com>
In-Reply-To: 
 <CA+TgmoYUdeCdGfk8H6Ni2obXVixLvYaDkRGtxKLEmaCVNffsVA@mail.gmail.com>
From: Lukas Fittl <lukas@fittl.com>
Date: Thu, 2 Apr 2026 19:32:36 -0700
Message-ID: 
 <CAP53PkyQS_aSWw_PouYj-zr5A_JUeKD0sCdKTkWpk0uFQMBcyw@mail.gmail.com>
Subject: Re: Add custom EXPLAIN options support to auto_explain
To: Robert Haas <robertmhaas@gmail.com>
Cc: Matheus Alcantara <matheusssilv97@gmail.com>,
 pgsql-hackers@lists.postgresql.org,
	Tom Lane <tgl@sss.pgh.pa.us>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAP53PkyQS_aSWw_PouYj-zr5A_JUeKD0sCdKTkWpk0uFQMBcyw%40mail.gmail.com>
Precedence: bulk

On Tue, Mar 31, 2026 at 9:19=E2=80=AFAM Robert Haas <robertmhaas@gmail.com>=
 wrote:
>
> On Mon, Mar 30, 2026 at 5:49=E2=80=AFPM Robert Haas <robertmhaas@gmail.co=
m> 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 =3D on
> auto_explain.log_min_duration =3D 0
> auto_explain.log_extension_options =3D '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=3D0.00..0.01 rows=3D1 width=3D4)
> 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

--=20
Lukas Fittl