From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jerry Brenner <jbrenner@guidewire.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: 2 json explain plans for the same query/plan - why does one have constants while the other has parameter markers?
Date: Fri, 08 Dec 2023 19:44:13 -0500
Message-ID: <1566070.1702082653@sss.pgh.pa.us> (raw)
In-Reply-To: <CACoKFYS1iNXYwud1g4iUjdM7+XHPXf2M=VKiCz-dWytjHamRTg@mail.gmail.com>
References: <CACoKFYS1iNXYwud1g4iUjdM7+XHPXf2M=VKiCz-dWytjHamRTg@mail.gmail.com>
Jerry Brenner <jbrenner@guidewire.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, jbrenner@guidewire.com, pgsql-performance@lists.postgresql.org
Subject: Re: 2 json explain plans for the same query/plan - why does one have constants while the other has parameter markers?
In-Reply-To: <1566070.1702082653@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox