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

Hi David,

On Sun, Sep 5, 2021 at 8:32 PM David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
wrote:

> On Sunday, September 5, 2021, Richard Michael <rmichael(at)edgeofthenet(dot)org>
> wrote:
>
>>
>> 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`.
>>
>>
> A query plan is not the same as the query text. While the executed plan
> can be generic or custom the query text is constant.
>

Thanks for drawing my attention to the difference between the query text
and query plan.

> If you want to see the difference between a generic and a custom plan you
> need to comprise a query that would actually have different custom and
> generic plans. Queries that don’t involve tables, indexes, or joins don’t
> have any choices to make with respect to how they are executed.
>

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 appreciate your point about query plans which may not involve choices;
thank you for mentioning it.

Regards,
Richard

> David J.
>
>