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

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