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

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