On Thu, Sep 9, 2021 at 7:39 PM Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
Richard Michael <rmichael@xxxxxxxxxxxxxxxx> 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
