Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux