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:
pc_message674c4226db3858b916bd6d363b52f9f1_fullqueryplanwithparametermarkers.json
Description: application/json
Attachment:
pc_message415bc88c610f40c448a9c7a3eb19b704_fullqueryplanwithconstants.json
Description: application/json
