MIME-Version: 1.0
References: 
 <CA+TgmoZ-Jh1T6QyWoCODMVQdhTUPYkaZjWztzP1En4=ZHoKPzw@mail.gmail.com>
 <CAKZiRmxtJAFG7e1+Vs9B8ngON=AOzJbuws+1ZeH4LsbJh5AzoQ@mail.gmail.com>
 <CA+TgmoY9Ne_Sh10u6LSPc3wvOQPLp3kF9nBp3nqJEG2JGF2QiA@mail.gmail.com>
 <CA+Tgmoa57S6mP=aTOXH2-gDAL4TMO1WbGgrHSg0s6J4zUH=04g@mail.gmail.com>
 <DEB1SAGVDX7S.JMV96X4R0RCS@gmail.com>
 <CA+Tgmoaf__2B0BUL+vrg28P+3buX=Ti-kybqkHiLTtFrrCfzuA@mail.gmail.com>
 <CA+TgmoYpcLNOuypOTdgCSLW7FuA=t6BtB3meTARHX2-Dj_81xQ@mail.gmail.com>
 <DEFNP3WX7BLX.1X7DS1P9X6JIF@nmfay.com>
 <CA+TgmoZjv9OyFu1Gkt78w0vWEti8S33w8trYHmErf-GMmGSi=w@mail.gmail.com>
 <DELPC4B56Y5A.3OBZ3WCH3T4VX@nmfay.com>
 <CA+TgmoaOSBQD9Ux4eG40w723ZN=c0J7p-+oX4+J8urUeyLMo5w@mail.gmail.com>
 <CAOYmi+=g+MMoOpWkk2weXWKJcKH0eKey8gKHHdH0dF4Tiawrhw@mail.gmail.com>
 <CA+TgmobwaT=PXPDDrgDup+jA8KHBbkxigtziD-zNzAKKkQYVgQ@mail.gmail.com>
 <CAOYmi+mOmEW=amDRQMfw6-Fb3ZmDEQFaJzwk8Bc8W8DzaP85XQ@mail.gmail.com>
 <CA+TgmoaX2AMW4cdFM3OngBJxmxpkdmzF33R7-CWhvRLfucbFMg@mail.gmail.com>
 <CAOYmi+k4AyWCQHK=XVF99KVDuFkqxcADao61OWGLxu0nRYMONQ@mail.gmail.com>
 <CA+TgmoZ0x3ym_oueXRWzbM_=6ucKoPZVGj3rRMLBDC_FnetXDw@mail.gmail.com>
 <CAP53Pkycc=7N2bLzVT3x+qE1JamvRZWev5tFjdLJ1+-AV3Di+Q@mail.gmail.com>
 <CA+TgmoaKhuD91RnazbRyGkmP7--JdNq8oNDC3UcgTZSWbMxC7w@mail.gmail.com>
 <CAP53Pkw5-wMEeDJXFmqo_RTyL_spzCXb7HHKrbSnQqokVoZcNQ@mail.gmail.com>
 <CA+Tgmob-69bzbdi3U_QtebqAf6u1y8js=5=oNK639csVe1VbhA@mail.gmail.com>
 <CA+TgmoaZMOikxK=LqS+Jn+835h9S139JLGk-3LyETVXw5W5j=w@mail.gmail.com>
 <1136161.1769654478@sss.pgh.pa.us>
 <CAP53PkwZ1ZTMARKg6iEfAw9qzBhkjBitj-9gr_Jvy7k2AwGgWA@mail.gmail.com>
 <CAMbWs4--NuEUFE_xTo991TRXaZryE29jarJPDnVxoaQOYdt7tA@mail.gmail.com>
 <CA+TgmobzR+XMGbRosVPbjHbSo4+cgJn=qZK6w05aF1sbj=C+9Q@mail.gmail.com>
 <CA+TgmoawzvCoZAwFS85tE5+c8vBkqgcS8ZstQ_ohjXQ9wGT9sw@mail.gmail.com>
 <CA+TgmoYS4ZCVAF2jTce=bMP0Oq_db_srocR4cZyO0OBp9oUoGg@mail.gmail.com>
 <CAK98qZ2RzbgCHrSg4zLkvpzyBam_X6te-KF8w1+_vON9BAVMEw@mail.gmail.com>
 <CA+TgmoaCdsuvNn6T6SfQ_0YD2Hh2+hgTXh9fTGHQhPg1zvy2rQ@mail.gmail.com>
 <CAK98qZ1zWzRB0ABG7ULzTeWKRR5C7-FxLqM-6v8wQDiFM+DNAg@mail.gmail.com>
 <CA+Tgmob7ozJAs5SU6bD2RfAt4w_AmsMGz-aaVA6WeLXHkBypOg@mail.gmail.com>
 <CAK98qZ1J42RoAsHnYWGPPmHziZmzmqE=Lp_O6WJ-9aKK2qjikA@mail.gmail.com>
 <CA+TgmoYjcBA6dw3nwiyfDzPXTCrxTZPXDMrc2TrDJcL1cPK6iA@mail.gmail.com>
 <CA+TgmoYru-vxoTKfwjQby30r2OkTXfb18Km_=VLs6qk8Akr0-g@mail.gmail.com>
 <CAKFQuwYxTMx1e1dvVLd5vs8CO2Xe__YRCW0m4v2UErG=5Lu7kQ@mail.gmail.com>
 <CA+TgmoaPH3rOmH1zw8s10Az3TQKRbDnF3dKYZCAFvx_DgFV5_Q@mail.gmail.com>
 <CAKFQuwZ7CRbvmJ60EjvEUJP8X9hWVCHJz8yJZ0-xRpYwL=fugA@mail.gmail.com>
 <CA+TgmoYsG2pAOG4YYWwnGsaL+QfOZopi_m+f_hZqBZ3meNEkjg@mail.gmail.com>
 <CAKFQuwY4i6xXCRHd=ccrFz0sQOfiWRxhobiBrzyK0rzu1-Y33Q@mail.gmail.com>
 <CA+Tgmobbj_TaCsYmr1grJBTRKaFaxFfotXn1T6LSXs9GQ8_Kyw@mail.gmail.com>
 <CA+TgmoZzM2i+p-Rxdphs4qx7sshn-kzxF91ASQ5duOo0dFRXLQ@mail.gmail.com>
 <CA+Tgmoau7yJtvbeH-0kPt1Q=Gt_ezRdgM35Q1=LT665U_86Etg@mail.gmail.com>
 <CA+TgmoYRJdDh7MPK-cLs4w4gmYqEaWM3Y0o+2-YAKfJ=5rHNyA@mail.gmail.com>
 <CAP53PkxpPsB+xtuC1zKt=6ANw8vHjX_SJe2k=0AguMAqaK67Lg@mail.gmail.com>
In-Reply-To: 
 <CAP53PkxpPsB+xtuC1zKt=6ANw8vHjX_SJe2k=0AguMAqaK67Lg@mail.gmail.com>
From: Robert Haas <robertmhaas@gmail.com>
Date: Thu, 19 Mar 2026 12:54:43 -0400
Message-ID: 
 <CA+TgmobbkxT2ZgjmXEDY3Rd7hf27J7BegYG8Zyy2ARecz5RnOQ@mail.gmail.com>
Subject: Re: pg_plan_advice
To: Lukas Fittl <lukas@fittl.com>
Cc: PostgreSQL Hackers <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/CA%2BTgmobbkxT2ZgjmXEDY3Rd7hf27J7BegYG8Zyy2ARecz5RnOQ%40mail.gmail.com>
Precedence: bulk

On Wed, Mar 18, 2026 at 3:00=E2=80=AFPM Lukas Fittl <lukas@fittl.com> wrote=
:
> I think we have similar problems elsewhere in Postgres where a large
> user input causes an even larger log output - e.g. a case I'm familiar
> with are complicated queries with long IN list inputs and their
> associated EXPLAIN plans being logged by auto_explain - I recently had
> a case where someone reported an OOM due to auto_explain trying to log
> a > 100 MB sized query plan.
>
> I think its actually less a problem with plan advice, since presumably
> we won't have ORMs generating plan advice, and even if we do it'd be
> per-table - so I think its unlikely a genuine use case would use 1000s
> of advice tags.
>
> That said, I also don't think super long long messages are actually
> helpful. I do wonder if we should have a more coarse GUC that limits
> DETAIL lines of any kind to a maximum length (e.g. 100 kB) across the
> board instead of special casing every emitter.

I think it would be difficult for generic code to do something
sensible when the message is really long. I mean, it could just cut it
off after N lines, but then you have no idea how much more there would
have been, and you probably want to tell the user something about
that. You could add a completely generic message to the end like "plus
10525 more lines of output that were truncated for display," but
that's pretty unsatisfying. If you want to show something contextually
appropriate, the implementation needs to be separate for each case
even if the limit is common. Anyway, the question here is not about
such a generic mechanism, but about whether somebody wants to argue
for sticking a limit of 100 on feedback messages on the theory that
log spam is bad, or whether it's fine as-is either because (a) the
likelihood of a significant number of people hitting that limit is
thought to be too low to worry about or (b) the likelihood of someone
wanting all of those messages (e.g. for machine-parsing) is thought to
be high enough that a limit is actually worse than no limit. I do not
really have a horse in the race, so if nobody else has a strong
opinion, I'm going to leave it alone for now and consider changing it
if a strong opinion materializes later.

> 1) What if we return the utilized advice string as a separate DefElem
> with a list of strings, and then the feedback just has to reference an
> index into that list? (though I suppose that doesn't actually save
> memory, now that I think that through -- unless we assume the caller
> already has the advice string, but I don't think we can rely on that)

I think that if we're using Integer nodes, it's just never going to be
very economical. We could use an IntList, which I believe would be
better, but there are a few complications that make me not like this
idea very much. One, what the feedback is actually about is a
reconstruction of a particular advice item into string form, not the
original string, e.g. if you input SEQ_SCAN(foo bar), the feedback
will be on SEQ_SCAN(foo) and SEQ_SCAN(bar). Two, even if you ignore
that, this would leave consumers of the data with the problem of
finding the end of the advice item unless you stored both starting and
ending indexes, which would have its own costs.

> 2) We could consider having separate DefElems for the different flag
> types (i.e. "feedback_failed", "feedback_match_full", etc), and then a
> list of strings attached to each - that'd save the nested DefElem and
> the Integer node

But it would also very often duplicate a bunch of the strings, which
seems likely to work out to a loss more often than not. You could
avoid that by have a list of strings per unique flag combination, but
that would be extra work to compute and I think it would be less
convenient for consumers. One user-visible consequence would be that
the advice feedback in EXPLAIN output would have much less to do with
the original order of the advice string.

--=20
Robert Haas
EDB: http://www.enterprisedb.com