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

From: Jerry Brenner <jbrenner(at)guidewire(dot)com>
To: pgsql-performance(at)lists(dot)postgresql(dot)org
Subject: 2 json explain plans for the same query/plan - why does one have constants while the other has parameter markers?
Date: 2023-12-09 00:23:41
Message-ID: CACoKFYS1iNXYwud1g4iUjdM7+XHPXf2M=VKiCz-dWytjHamRTg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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?

Examples of the parameter markers:
"Recheck Cond": "*((destinationid = $1)* AND (contactid IS
NOT NULL) AND (status = $2))",
"Index Cond": "*((destinationid = $1)* AND (contactid
IS NOT NULL) AND (status = $2))",

What we normally see:
"Recheck Cond": "(*(destinationid = 67) *AND (contactid IS
NOT NULL) AND (status = 1))",
"Index Cond": "*((destinationid = 67) *AND (contactid
IS NOT NULL) AND (status = 1))",

The full query text:

SELECT /* ISNULL:pc_message.FrozenSetID:, KeyTable:pc_message; */ qRoot.ID
col0, qRoot.CreationTime col1
FROM pc_message qRoot
WHERE qRoot.DestinationID = $1 AND qRoot.Status = $2 AND qRoot.contactID IS
NOT NULL AND qRoot.FrozenSetID IS NULL AND qRoot.SendOrder IN
(
SELECT MIN (qRoot0.SendOrder) col0
FROM pc_message qRoot0
WHERE qRoot0.DestinationID = $3 AND qRoot0.contactID =
qRoot.contactID)
ORDER BY col1 ASC, col0 ASC LIMIT 100000

Thanks,
Jerry

Attachment Content-Type Size
pc_message674c4226db3858b916bd6d363b52f9f1_fullqueryplanwithparametermarkers.json application/json 11.1 KB
pc_message415bc88c610f40c448a9c7a3eb19b704_fullqueryplanwithconstants.json application/json 11.1 KB

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2023-12-09 00:44:13 Re: 2 json explain plans for the same query/plan - why does one have constants while the other has parameter markers?
Previous Message Jerry Brenner 2023-12-08 23:09:52 Question about semantics of $ variables in json explain plans in 13