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@xxxxxxxxxxxxx> wrote:
Jerry Brenner <jbrenner@xxxxxxxxxxxxx> 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
