Re: Add custom EXPLAIN options support to auto_explain

On Tue, Mar 31, 2026 at 9:19 AM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
> On Mon, Mar 30, 2026 at 5:49 PM Robert Haas <robertmhaas(at)gmail(dot)com> 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