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