Re: 2 json explain plans for the same query/plan - why does one have constants while the other has parameter markers?

Can you consider adding an attribute to the explain plan json in a future
release (to plan?) to denote if the plan is a "custom" vs "generic" plan?
The use of $N variables for both parameter markers and InitPlan and SubPlan
makes it harder to programmatically determine the type of plan (and in our
case tell if 2 plans only differ by "custom" vs "generic").

We use numeric constants in our queries in a small number of cases where we
know that there's no potential PII, there's a small number of values and
that there's a high probability that the data is skewed. pc_message
contains messages to be sent to external systems and hence is a volatile
table and the data in the DestinationID column can be highly skewed. In
theory, could using a constant instead of a bind variable for this
predicate help the optimizer?

Thanks,
Jerry

On Fri, Dec 8, 2023 at 5:04 PM Jerry Brenner <jbrenner(at)guidewire(dot)com> wrote:

> Thanks for the quick response! That was very helpful!
> My impression is that almost all of the plans being captured are
> "custom", but now I know that I need to look closer. We also store the
> execution times, so we can look at the execution order for queries that are
> executed often enough to seem like they should stay in the cache. The
> addition of the new timestamp columns in pg_stat_statements in 17 will also
> help us get a better sense of how long the query had been in the cache.
>
> On Fri, Dec 8, 2023 at 4:44 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> Jerry Brenner <jbrenner(at)guidewire(dot)com> writes:
>> > We are currently on 13. We are capturing the explain plans for query
>> > executions taking 1 second or longer and storing the json files. We are
>> > most of the way through implementing a home grown solution to generate a
>> > consistent hash value for a query plan, so we can find queries with
>> > multiple plans. I've attached 2 query plans that we've captured that
>> > differ in a seemingly strange way. (All executions are from the same
>> exact
>> > code path.) One of the plans has parameter markers in the predicates in
>> > the values for "Recheck Cond" and "Index Cond", while the other does
>> not.
>> > Any insight into why we are seeing parameter markers in the body of the
>> > query plan?
>>
>> The one with parameter markers is a "generic" plan for a parameterized
>> query. When you get a plan without parameter markers for the same
>> input query, that's a "custom" plan in which concrete values of the
>> parameters have been substituted, possibly allowing const-simplification
>> and more accurate rowcount estimates. The backend will generally try
>> custom plans a few times and then try a generic plan to see if that's
>> meaningfully slower -- if not, replanning each time is deemed to be
>> wasteful.
>>
>> regards, tom lane
>>
>>