Details
-
Bug
-
Resolution: Fixed
-
Major
-
6.6.1
-
1
Description
Investigate why 6.6.1 (couchbase-server-6.6.1-9213.x86_64) IntersectScan is greater than 5x times slower than 6.0.1 (couchbase-server-6.0.1-2037.x86_64)
The plans are different but need to know root cause.
Repro:
Install travel sample OR use any bucket and change the repro
|
|
|
CREATE INDEX ix30 ON `travel-sample`(ff.id, status,requester.id) WHERE type = "token"; |
CREATE INDEX ix31 ON `travel-sample`(type, guid,`requester.code`,status) WHERE type = "token"; |
INSERT INTO `travel-sample`
|
VALUES(UUID(), {"type":"token","guid":"58ccfc13-41ca-420a-8419-e1ab02c7b6e0", "ff":{"id":"667F17E73764"},"requester":{"id":"30273"}, "status":"Active"}), |
VALUES(UUID(), {"type":"token","guid":"58ccfc13-41ca-420a-8419-e1ab02c7b6e0", "ff":{"id":"667F17E73765"},"requester":{"id":"30274"}, "status":"Open"}), |
VALUES(UUID(), {"type":"token","guid":"58ccfc13-41ca-420a-8419-e1ab02c7b6e0", "ff":{"id":"667F17E73765"},"requester":{"id":"30274"}, "status":"Open"}), |
VALUES(UUID(), {"type":"token","guid":"58ccfc13-41ca-420a-8419-e1ab02c7b6e0", "ff":{"id":"667F17E73765"},"requester":{"id":"30274"}, "status":"Open"}), |
VALUES(UUID(), {"type":"token","guid":"58ccfc13-41ca-420a-8419-e1ab02c7b6e0", "ff":{"id":"667F17E73765"},"requester":{"id":"30274"}, "status":"Open"}), |
VALUES(UUID(), {"type":"token","guid":"58ccfc13-41ca-420a-8419-e1ab02c7b6e0", "ff":{"id":"667F17E73765"},"requester":{"id":"30274"}, "status":"Open"}); |
|
|
SELECT *
|
FROM `travel-sample`
|
WHERE type = "token" |
AND guid = "58ccfc13-41ca-420a-8419-e1ab02c7b6e0" |
AND requester.id = "30273" |
AND ff.id = "667F17E73764" |
AND (status = 'Active' OR status = 'Suspend' OR status='PendingActivate'); |
6.0.1 Plan
EXPLAIN SELECT * FROM `travel-sample` WHERE type = "token" AND guid = "58ccfc13-41ca-420a-8419-e1ab02c7b6e0" AND requester.id = "30273" AND ff.id = "667F17E73764" AND (status = 'Active' OR status = 'Suspend' OR status='PendingActivate'); |
{
|
"requestID": "511e53ba-41f3-446f-8a7b-92e98357d597", |
"signature": "json", |
"results": [ |
{
|
"plan": { |
"#operator": "Sequence", |
"~children": [ |
{
|
"#operator": "UnionScan", |
"scans": [ |
{
|
"#operator": "IntersectScan", |
"scans": [ |
{
|
"#operator": "IndexScan3", |
"index": "ix31", |
"index_id": "9376b8547947a2d9", |
"index_projection": { |
"primary_key": true |
},
|
"keyspace": "travel-sample", |
"namespace": "default", |
"spans": [ |
{
|
"exact": true, |
"range": [ |
{
|
"high": "\"token\"", |
"inclusion": 3, |
"low": "\"token\"" |
},
|
{
|
"high": "\"58ccfc13-41ca-420a-8419-e1ab02c7b6e0\"", |
"inclusion": 3, |
"low": "\"58ccfc13-41ca-420a-8419-e1ab02c7b6e0\"" |
}
|
]
|
}
|
],
|
"using": "gsi" |
},
|
{
|
"#operator": "IndexScan3", |
"index": "ix30", |
"index_id": "d3dc87d1953bd476", |
"index_projection": { |
"primary_key": true |
},
|
"keyspace": "travel-sample", |
"namespace": "default", |
"spans": [ |
{
|
"exact": true, |
"range": [ |
{
|
"high": "\"667F17E73764\"", |
"inclusion": 3, |
"low": "\"667F17E73764\"" |
},
|
{
|
"high": "\"Active\"", |
"inclusion": 3, |
"low": "\"Active\"" |
},
|
{
|
"high": "\"30273\"", |
"inclusion": 3, |
"low": "\"30273\"" |
}
|
]
|
}
|
],
|
"using": "gsi" |
}
|
]
|
},
|
{
|
"#operator": "IntersectScan", |
"scans": [ |
{
|
"#operator": "IndexScan3", |
"index": "ix31", |
"index_id": "9376b8547947a2d9", |
"index_projection": { |
"primary_key": true |
},
|
"keyspace": "travel-sample", |
"namespace": "default", |
"spans": [ |
{
|
"exact": true, |
"range": [ |
{
|
"high": "\"token\"", |
"inclusion": 3, |
"low": "\"token\"" |
},
|
{
|
"high": "\"58ccfc13-41ca-420a-8419-e1ab02c7b6e0\"", |
"inclusion": 3, |
"low": "\"58ccfc13-41ca-420a-8419-e1ab02c7b6e0\"" |
}
|
]
|
}
|
],
|
"using": "gsi" |
},
|
{
|
"#operator": "IndexScan3", |
"index": "ix30", |
"index_id": "d3dc87d1953bd476", |
"index_projection": { |
"primary_key": true |
},
|
"keyspace": "travel-sample", |
"namespace": "default", |
"spans": [ |
{
|
"exact": true, |
"range": [ |
{
|
"high": "\"667F17E73764\"", |
"inclusion": 3, |
"low": "\"667F17E73764\"" |
},
|
{
|
"high": "\"Suspend\"", |
"inclusion": 3, |
"low": "\"Suspend\"" |
},
|
{
|
"high": "\"30273\"", |
"inclusion": 3, |
"low": "\"30273\"" |
}
|
]
|
}
|
],
|
"using": "gsi" |
}
|
]
|
},
|
{
|
"#operator": "IntersectScan", |
"scans": [ |
{
|
"#operator": "IndexScan3", |
"index": "ix30", |
"index_id": "d3dc87d1953bd476", |
"index_projection": { |
"primary_key": true |
},
|
"keyspace": "travel-sample", |
"namespace": "default", |
"spans": [ |
{
|
"exact": true, |
"range": [ |
{
|
"high": "\"667F17E73764\"", |
"inclusion": 3, |
"low": "\"667F17E73764\"" |
},
|
{
|
"high": "\"PendingActivate\"", |
"inclusion": 3, |
"low": "\"PendingActivate\"" |
},
|
{
|
"high": "\"30273\"", |
"inclusion": 3, |
"low": "\"30273\"" |
}
|
]
|
}
|
],
|
"using": "gsi" |
},
|
{
|
"#operator": "IndexScan3", |
"index": "ix31", |
"index_id": "9376b8547947a2d9", |
"index_projection": { |
"primary_key": true |
},
|
"keyspace": "travel-sample", |
"namespace": "default", |
"spans": [ |
{
|
"exact": true, |
"range": [ |
{
|
"high": "\"token\"", |
"inclusion": 3, |
"low": "\"token\"" |
},
|
{
|
"high": "\"58ccfc13-41ca-420a-8419-e1ab02c7b6e0\"", |
"inclusion": 3, |
"low": "\"58ccfc13-41ca-420a-8419-e1ab02c7b6e0\"" |
}
|
]
|
}
|
],
|
"using": "gsi" |
}
|
]
|
}
|
]
|
},
|
{
|
"#operator": "Fetch", |
"keyspace": "travel-sample", |
"namespace": "default" |
},
|
{
|
"#operator": "Parallel", |
"~child": { |
"#operator": "Sequence", |
"~children": [ |
{
|
"#operator": "Filter", |
"condition": "((((((`travel-sample`.`type`) = \"token\") and ((`travel-sample`.`guid`) = \"58ccfc13-41ca-420a-8419-e1ab02c7b6e0\")) and (((`travel-sample`.`requester`).`id`) = \"30273\")) and (((`travel-sample`.`ff`).`id`) = \"667F17E73764\")) and ((((`travel-sample`.`status`) = \"Active\") or ((`travel-sample`.`status`) = \"Suspend\")) or ((`travel-sample`.`status`) = \"PendingActivate\")))" |
},
|
{
|
"#operator": "InitialProject", |
"result_terms": [ |
{
|
"expr": "self", |
"star": true |
}
|
]
|
},
|
{
|
"#operator": "FinalProject" |
}
|
]
|
}
|
}
|
]
|
},
|
"text": "SELECT * FROM `travel-sample` WHERE type = \"token\" AND guid = \"58ccfc13-41ca-420a-8419-e1ab02c7b6e0\" AND requester.id = \"30273\" AND ff.id = \"667F17E73764\" AND (status = 'Active' OR status = 'Suspend' OR status='PendingActivate');" |
}
|
],
|
"status": "success", |
"metrics": { |
"elapsedTime": "8.427286ms", |
"executionTime": "8.298114ms", |
"resultCount": 1, |
"resultSize": 13914 |
}
|
}
|
cbq> SELECT * FROM `travel-sample` WHERE type = "token" AND guid = "58ccfc13-41ca-420a-8419-e1ab02c7b6e0" AND requester.id = "30273" AND ff.id = "667F17E73764" AND (status = 'Active' OR status = 'Suspend' OR status='PendingActivate'); |
{
|
"requestID": "66bf691c-15d7-4a9d-8b32-1dd8f41a23bb", |
"signature": { |
"*": "*" |
},
|
"results": [ |
{
|
"travel-sample": { |
"ff": { |
"id": "667F17E73764" |
},
|
"guid": "58ccfc13-41ca-420a-8419-e1ab02c7b6e0", |
"requester": { |
"id": "30273" |
},
|
"status": "Active", |
"type": "token" |
}
|
}
|
],
|
"status": "success", |
"metrics": { |
"elapsedTime": "7.495216ms", |
"executionTime": "7.343701ms", |
"resultCount": 1, |
"resultSize": 356 |
}
|
}
|
6.6.1 Plan
cbq> EXPLAIN SELECT * FROM `travel-sample` WHERE type = "token" AND guid = "58ccfc13-41ca-420a-8419-e1ab02c7b6e0" AND requester.id = "30273" AND ff.id = "667F17E73764" AND (status = 'Active' OR status = 'Suspend' OR status='PendingActivate'); |
{
|
"requestID": "28eca415-7498-445e-b125-e7502ec0b92b", |
"signature": "json", |
"results": [ |
{
|
"plan": { |
"#operator": "Sequence", |
"~children": [ |
{
|
"#operator": "IntersectScan", |
"scans": [ |
{
|
"#operator": "IndexScan3", |
"index": "ix31", |
"index_id": "9064232cd58ad2b1", |
"index_projection": { |
"primary_key": true |
},
|
"keyspace": "travel-sample", |
"namespace": "default", |
"spans": [ |
{
|
"exact": true, |
"range": [ |
{
|
"high": "\"token\"", |
"inclusion": 3, |
"low": "\"token\"" |
},
|
{
|
"high": "\"58ccfc13-41ca-420a-8419-e1ab02c7b6e0\"", |
"inclusion": 3, |
"low": "\"58ccfc13-41ca-420a-8419-e1ab02c7b6e0\"" |
},
|
{
|
"inclusion": 0 |
},
|
{
|
"high": "\"Active\"", |
"inclusion": 3, |
"low": "\"Active\"" |
}
|
]
|
},
|
{
|
"exact": true, |
"range": [ |
{
|
"high": "\"token\"", |
"inclusion": 3, |
"low": "\"token\"" |
},
|
{
|
"high": "\"58ccfc13-41ca-420a-8419-e1ab02c7b6e0\"", |
"inclusion": 3, |
"low": "\"58ccfc13-41ca-420a-8419-e1ab02c7b6e0\"" |
},
|
{
|
"inclusion": 0 |
},
|
{
|
"high": "\"Suspend\"", |
"inclusion": 3, |
"low": "\"Suspend\"" |
}
|
]
|
},
|
{
|
"exact": true, |
"range": [ |
{
|
"high": "\"token\"", |
"inclusion": 3, |
"low": "\"token\"" |
},
|
{
|
"high": "\"58ccfc13-41ca-420a-8419-e1ab02c7b6e0\"", |
"inclusion": 3, |
"low": "\"58ccfc13-41ca-420a-8419-e1ab02c7b6e0\"" |
},
|
{
|
"inclusion": 0 |
},
|
{
|
"high": "\"PendingActivate\"", |
"inclusion": 3, |
"low": "\"PendingActivate\"" |
}
|
]
|
}
|
],
|
"using": "gsi" |
},
|
{
|
"#operator": "IndexScan3", |
"index": "ix30", |
"index_id": "251bb86e7f780704", |
"index_projection": { |
"primary_key": true |
},
|
"keyspace": "travel-sample", |
"namespace": "default", |
"spans": [ |
{
|
"exact": true, |
"range": [ |
{
|
"high": "\"667F17E73764\"", |
"inclusion": 3, |
"low": "\"667F17E73764\"" |
},
|
{
|
"high": "\"Active\"", |
"inclusion": 3, |
"low": "\"Active\"" |
},
|
{
|
"high": "\"30273\"", |
"inclusion": 3, |
"low": "\"30273\"" |
}
|
]
|
},
|
{
|
"exact": true, |
"range": [ |
{
|
"high": "\"667F17E73764\"", |
"inclusion": 3, |
"low": "\"667F17E73764\"" |
},
|
{
|
"high": "\"Suspend\"", |
"inclusion": 3, |
"low": "\"Suspend\"" |
},
|
{
|
"high": "\"30273\"", |
"inclusion": 3, |
"low": "\"30273\"" |
}
|
]
|
},
|
{
|
"exact": true, |
"range": [ |
{
|
"high": "\"667F17E73764\"", |
"inclusion": 3, |
"low": "\"667F17E73764\"" |
},
|
{
|
"high": "\"PendingActivate\"", |
"inclusion": 3, |
"low": "\"PendingActivate\"" |
},
|
{
|
"high": "\"30273\"", |
"inclusion": 3, |
"low": "\"30273\"" |
}
|
]
|
}
|
],
|
"using": "gsi" |
}
|
]
|
},
|
{
|
"#operator": "Fetch", |
"keyspace": "travel-sample", |
"namespace": "default" |
},
|
{
|
"#operator": "Parallel", |
"~child": { |
"#operator": "Sequence", |
"~children": [ |
{
|
"#operator": "Filter", |
"condition": "((((((`travel-sample`.`type`) = \"token\") and ((`travel-sample`.`guid`) = \"58ccfc13-41ca-420a-8419-e1ab02c7b6e0\")) and (((`travel-sample`.`requester`).`id`) = \"30273\")) and (((`travel-sample`.`ff`).`id`) = \"667F17E73764\")) and ((((`travel-sample`.`status`) = \"Active\") or ((`travel-sample`.`status`) = \"Suspend\")) or ((`travel-sample`.`status`) = \"PendingActivate\")))" |
},
|
{
|
"#operator": "InitialProject", |
"result_terms": [ |
{
|
"expr": "self", |
"star": true |
}
|
]
|
},
|
{
|
"#operator": "FinalProject" |
}
|
]
|
}
|
}
|
]
|
},
|
"text": "SELECT * FROM `travel-sample` WHERE type = \"token\" AND guid = \"58ccfc13-41ca-420a-8419-e1ab02c7b6e0\" AND requester.id = \"30273\" AND ff.id = \"667F17E73764\" AND (status = 'Active' OR status = 'Suspend' OR status='PendingActivate');" |
}
|
],
|
"status": "success", |
"metrics": { |
"elapsedTime": "43.322202ms", |
"executionTime": "43.215558ms", |
"resultCount": 1, |
"resultSize": 9970 |
}
|
}
|
cbq>
|
cbq> SELECT * FROM `travel-sample` WHERE type = "token" AND guid = "58ccfc13-41ca-420a-8419-e1ab02c7b6e0" AND requester.id = "30273" AND ff.id = "667F17E73764" AND (status = 'Active' OR status = 'Suspend' OR status='PendingActivate'); |
{
|
"requestID": "6f67ccdd-5a5a-41af-8195-3f19536f9bd7", |
"signature": { |
"*": "*" |
},
|
"results": [ |
{
|
"travel-sample": { |
"ff": { |
"id": "667F17E73764" |
},
|
"guid": "58ccfc13-41ca-420a-8419-e1ab02c7b6e0", |
"requester": { |
"id": "30273" |
},
|
"status": "Active", |
"type": "token" |
}
|
}
|
],
|
"status": "success", |
"metrics": { |
"elapsedTime": "43.809462ms", |
"executionTime": "43.675582ms", |
"resultCount": 1, |
"resultSize": 308 |
}
|
}
|
cbq>
|