Re: 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