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

On 9/5/21 9:35 AM, Richard Michael wrote:
> TL;DR --
>
> 1/ My basic prepared statement doesn't seem to start out with a custom 
> plan
> (i.e., no parameter symbols in query text).
>
> 2/ EXPLAIN EXECUTE of prepared statement doesn't show query text, as the
> documentation seems to imply.  Should it?
>
> 3/ How can I observe the effect of plan_cache_mode?
>     e.g., Possible plan change from custom to generic
>
> Apologies if this belongs on -novice.
>
>
> Details --
>
> I am trying to understand `plan_cache_mode`, related to a report of a
> performance problem (resolved by changing `auto` to
> `force_custom_plan`).  I do not have access to the server or data in the
> report, so I am trying to reproduce a similar situation myself. I am
> using PostgreSQL 12.
>
> My first step is to observe the change in the plan, from custom to
> generic, after the first five queries.  I have unsuccessfully tried to
> do this with both `EXPLAIN` and the `auto_explain` extension.
>
> I would appreciate help trying to understand what's happening and what
> I've misunderstood.
>
> (After I see the plan changing, I'll use more complicated data and
> queries to investigate different performance scenarios. Eventually, I'm
> interested in queries using the extended protocol. I'm unsure if 
> parse/bind/execute
> will go through the same server code path as `PREPARE ... ; EXECUTE ..`.)
>
> The `PREPARE` documentation
> (https://www.postgresql.org/docs/12/sql-prepare.html 
> <https://www.postgresql.org/docs/12/sql-prepare.html>) indicates:
>
>  1/ Regarding `plan_cache_mode`, "... the first five executions are
>  done with custom plans ..."
>
>  2/ Regarding `EXPLAIN EXECUTE ...`, "To examine the query plan
>  PostgreSQL is using for a prepared statement, use EXPLAIN (...).  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."
>
>
> Using psql, I tried preparing and explaining a very basic query: `SELECT
> $1 AS data`.
>
>   Note: Wireshark revealed psql uses simple queries (`PREPARE ...`,
>   `EXPLAIN EXECUTE ...` are `Q` messages), not the extended protocol
>   (i.e., parse, bind, execute).  I mention this because previous list
>   posts mention some libpq prepared statement functions do not result in
>   the planning heuristic used by `plan_cache_mode`.
>
>
> Based on the documentation, I expected the first planned query text to
> be: `SELECT 10 AS data`, since it should be a custom plan with
> substituted values.  However, the query text always contains a parameter
> symbol: `SELECT $1 AS data`.
>
>
> My questions:
>
>
> 1/ Slightly related, the `EXPLAIN EXECUTE(...)` output does not show the
> query text, as the documentation seems to suggest it should (so one may
> look for parameter symbols).  Why not?
>
> (Although, none of the documented EXPLAIN options mentions query text
> display.  So perhaps it never does?)
>
>   ```
>   ~# PREPARE one_param AS SELECT $1 AS data;
>   ~# EXPLAIN EXECUTE one_param(10);
>                     QUERY PLAN
>     -------------------------------------------
>      Result  (cost=0.00..0.01 rows=1 width=32)
>     (1 row)
>   ```
>
> 2/ The query text was logged by `auto_explain`.  However, it contains a
> parameter symbol; why?  Also, why is the logged query a `PREPARE` 
> statement
> (as if a new prepared statement is being created), instead of only the
> `SELECT ..` which was executed?
>
>   ```
>   LOG:  statement: EXPLAIN EXECUTE one_param(10);
>   LOG:  duration: 0.000 ms  plan:
>         Query Text: PREPARE one_param AS SELECT $1 AS data;
>         Result  (cost=0.00..0.01 rows=1 width=32)
>           Output: '10'::text
>   ```
>
>
> Let me know if I should post my postgresql.conf and `auto_explain` 
> settings.
>
> Thanks for any advice,
> Richard


Well, some other databases that shall remain unnamed have a thing called 
"bind variable peeking". Essentially, the database instance uses the 
first set of bind variables to create the execution plan. That is far 
from optimal because the first set of the bind variables encountered by 
the instance may not be representative of  what the vast majority of the 
work is comprised of. That particular database also has tricks like 
dynamic statistics sampling, cardinality feedback and adaptive plans (if 
the database finds a cheaper plan, it can substitute it on the go). All 
of that mess results in high degree of unpredictability. With that 
particular database, plan stability is like El Dorado: everybody is 
looking for it but nobody can find it. There are outlines, baselines and 
SQL patches, none of which is quite satisfactory.

Postgres has a different method: it executes the same statement with 5 
different sets of bind variables and if it finds a plan that is cheaper 
than the generic plan, it caches it and uses it in the future. Caching 
is pertinent for one session only. There is no cursor sharing between 
sessions. If it doesn't find such a plan, it uses a generic plan with 
hard coded values instead of the bind values and table statistics. 
People switching from Oracle, me being one of those, frequently make 
mistake of using bind variables in Postgres. Sometimes, there is no 
choice, for instance if your app uses an application server and an ORM. 
Postgres doesn't have the memory structure called "shared pool" and 
cursors and compiled procedures are not cached. That means that by using 
Postgres, you are deprived of all the fun managing the cursor caching, 
shared pool, database statistics and latches (don't ask, please). There 
is a small consolation that you get to keep a ton of money in your 
pocket. You also can have a stable plan. There are no adaptive plans or 
cursor sharing.

Frank Pachot, an Oracle Ace, has an excellent article on the subject:

https://franckpachot.medium.com/postgresql-bind-variable-peeking-fb4be4942252

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

Based on the documentation, I expected the first planned query text to
be: `SELECT 10 AS data`, since it should be a custom plan with
substituted values.  However, the query text always contains a parameter
symbol: `SELECT $1 AS data`.

On 9/6/21 2:26 AM, Laurenz Albe wrote:
> "Bind variables" just being an Oraclism for parameters, it is*not*  a
> mistake to use them in PostgreSQL.

Actually, it is a mistake because they don't give you any performance 
benefit and can potentially worsen the performance. There is no cursor 
sharing and generic plans can be much worse than "custom" plans, 
generated with the actual values. The only reason for using bind 
variables/parameters is to protect yourself from SQL injection. Creating 
SQL dynamically from input is the recipe for the "little Bobby Tables" 
situation: https://xkcd.com/327/


-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

Richard Michael <rmichael@edgeofthenet.org> writes:
> After reading Laurenz's reply, I experimented again with `PREPARE
> basic(int) AS SELECT $1 AS number;" and the query plan logged in the log
> file (by auto_explain) does indeed show "Output: $1" on the sixth EXPLAIN
> EXECUTE!  Sorry I missed this earlier.  (The EXPLAIN EXECUTE output itself
> does not contain an "Output:" line, so I didn't notice this lack of
> parameter substitution in psql.)

I think EXPLAIN VERBOSE would add that bit of detail.

            regards, tom lane

Richard Michael <rmichael@edgeofthenet.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.)

            regards, tom lane