Re: How to observe plan_cache_mode transition from custom to generic plan?

On Thu, Sep 9, 2021 at 7:39 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Richard Michael <rmichael(at)edgeofthenet(dot)org> writes:
> > Would a tiny patch to the PREPARE docs be accepted? I'd like to help
> > clarify investigating `plan_cache_mode` for future readers.
>
> What did you have in mind?
>
> (I'm kind of allergic to documenting the plan-caching behavior in too
> much detail, because I keep expecting us to change it. On the other
> hand, nobody's put forward any improvement proposals.)
>

For reference, currently in PREPARE docs:

"If a generic plan is in use, it will contain parameter symbols $n, while a
custom plan will have the supplied parameter values substituted into it."

Here is a rough stab at a few additional sentences which hopefully will not
induce anaphylaxis; my motivation follows.

"If a generic plan is in use, it will contain parameter symbols $n, while a
custom plan will have the supplied parameter values substituted into plan
nodes which use the parameter, such as filters or conditions. If the
parameters are used in the output, `EXPLAIN VERBOSE` can be helpful, as it
will display query output. Note the query text (displayed by
`auto_explain`) is constant, and will not have parameters substituted, even
when a generic plan is used."

(Perhaps mentioning auto_explain is overly specific. Although, no other
command or tooling prints the query text. This note could or should go in
the auto_explain docs.)

As you noticed up-thread, I was testing with a very basic query: `SELECT $1
AS number`, for which EXPLAIN EXECUTE shows no parameter related values (it
occurs only in the output, requiring VERBOSE).

Also, I was testing from several clients: psql, my test C program, and
Elixir. For simplicity (oops), I enabled `auto_explain`. Auto-explain
prints `plan: ... Query Text: SELECT $1 AS number`, and I see
`ExplainQueryText()` was added to commands/explain.c years ago for this
purpose.

In my situation, only the auto_explain output was displaying parameter
symbols ($1), in the query text, and I allowed myself to be misled. (In
auto_explain output, the query text seems to be "part of the plan", and
hence I thought it would have values substituted).

Re-reading the original sentence, I concede it is sufficient given what I
now know (especially static, non-substituted query text). It can
definitely be argued that: (1) I don't know the planner or EXPLAIN or
auto_explain well enough to be competent at this type of investigation, and
(2) the PREPARE documentation did not mention `auto_explain`, so I should
not have used it, and followed the documentation precisely. (The static
query text matter is a bit subtle, I think.)

However, I would also say the documentation could nudge the reader in a
helpful direction, and my general position on documentation is that it's
for newcomers not experts (who don't need any :). I'd like to believe
those additional sentences would have spared you all this thread.

Aside, might EXPLAIN VERBOSE be enhanced to also print Query Text, so that
`auto_explain` really behaves like "automatic explain"? I note only
`auto_explain` uses the function added to explain.c.

Regards,
richard

> regards, tom lane
>