Details
-
Bug
-
Resolution: Fixed
-
Major
-
7.1.6, 7.2.6
-
Untriaged
-
0
-
No
Description
When CBO is on and UPDATE STATISTICS have been run, for certain UNNEST queries an intersect scan may be used and runs very slowly. When CBO is not used, we could also choose an intersect scan by RBO but that runs much faster.
Repro:
UPSERT INTO default (KEY k, VALUE v)
|
SELECT "k"||TO_STR(d) AS k ,
|
{"type" : "account",
|
"documents": [{"status": CASE WHEN MOD(d, 20) = 0 THEN "complete" ELSE "active" END}],
|
"accountId": TO_STR(d)
|
} AS v
|
FROM ARRAY_RANGE(1,32000) AS d;
|
|
|
CREATE INDEX idx_accountId ON default(accountId) WHERE type = "account";
|
CREATE INDEX adv_ALL_documents_status_type ON default(ALL ARRAY v.status FOR v IN documents END) WHERE type = "account";
|
CREATE INDEX idx_document_nested ON default(DISTINCT ARRAY doc_data FOR doc_data IN documents END) WHERE type = "account";
|
|
|
UPDATE STATISTICS FOR default INDEX(idx_accountId, adv_ALL_documents_status_type);
|
|
|
EXPLAIN SELECT DISTINCT v.*
|
FROM default AS d
|
UNNEST d.documents AS v
|
WHERE d.type = "account"
|
AND d.accountId = "1000"
|
AND v.status <> "complete"
|
LIMIT 100
|
OFFSET 0;
|
The plan looks like:
{
|
"requestID": "84d2406f-c7ff-484b-9488-e7618a4c0959",
|
"signature": "json",
|
"results": [
|
{
|
"cardinality": 0.4622537369190375,
|
"cost": 2974.4702206444317,
|
"plan": {
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "IntersectScan",
|
"optimizer_estimates": {
|
"cardinality": 0.4865828809674079,
|
"cost": 2968.32059400248,
|
"fr_cost": 12.138581858526946,
|
"size": 6
|
},
|
"scans": [
|
{
|
"#operator": "IndexScan3",
|
"as": "d",
|
"index": "idx_accountId",
|
"index_id": "881dc3956bccc1b4",
|
"index_projection": {
|
"primary_key": true
|
},
|
"keyspace": "default",
|
"namespace": "default",
|
"optimizer_estimates": {
|
"cardinality": 1,
|
"cost": 12.127859651946293,
|
"fr_cost": 12.127859651946293,
|
"size": 6
|
},
|
"spans": [
|
{
|
"exact": true,
|
"range": [
|
{
|
"high": "\"1000\"",
|
"inclusion": 3,
|
"index_key": "`accountId`",
|
"low": "\"1000\""
|
}
|
]
|
}
|
],
|
"using": "gsi"
|
},
|
{
|
"#operator": "DistinctScan",
|
"optimizer_estimates": {
|
"cardinality": 15570.165608076088,
|
"cost": 2968.32059400248,
|
"fr_cost": 12.138581858526946,
|
"size": 6
|
},
|
"scan": {
|
"#operator": "IndexScan3",
|
"as": "d",
|
"index": "adv_ALL_documents_status_type",
|
"index_id": "15aa0d18ca72b274",
|
"index_projection": {
|
"primary_key": true
|
},
|
"keyspace": "default",
|
"namespace": "default",
|
"optimizer_estimates": {
|
"cardinality": 21332.666666666664,
|
"cost": 2741.551236761349,
|
"fr_cost": 12.12795171271421,
|
"size": 6
|
},
|
"spans": [
|
{
|
"exact": true,
|
"range": [
|
{
|
"high": "\"complete\"",
|
"inclusion": 0,
|
"index_key": "(all (array (`v`.`status`) for `v` in `documents` end))",
|
"low": "null"
|
}
|
]
|
},
|
{
|
"exact": true,
|
"range": [
|
{
|
"inclusion": 0,
|
"index_key": "(all (array (`v`.`status`) for `v` in `documents` end))",
|
"low": "\"complete\""
|
}
|
]
|
}
|
],
|
"using": "gsi"
|
}
|
}
|
]
|
},
|
{
|
"#operator": "Fetch",
|
"as": "d",
|
"keyspace": "default",
|
"namespace": "default",
|
"optimizer_estimates": {
|
"cardinality": 0.4865828809674079,
|
"cost": 2974.4182109228223,
|
"fr_cost": 18.236198778869046,
|
"size": 113
|
}
|
},
|
{
|
"#operator": "Parallel",
|
"~child": {
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "Filter",
|
"condition": "(((`d`.`type`) = \"account\") and ((`d`.`accountId`) = \"1000\") and is_array((`d`.`documents`)))",
|
"optimizer_estimates": {
|
"cardinality": 0.4865828809674079,
|
"cost": 2974.4233833697967,
|
"fr_cost": 18.24137122584371,
|
"size": 113
|
}
|
},
|
{
|
"#operator": "Unnest",
|
"as": "v",
|
"expr": "(`d`.`documents`)",
|
"filter": "((not ((`v`.`status`) = \"complete\")) and (`v` is not missing))",
|
"optimizer_estimates": {
|
"cardinality": 0.4865828809674079,
|
"cost": 2974.44924560467,
|
"fr_cost": 18.267233460717033,
|
"size": 113
|
}
|
}
|
]
|
}
|
},
|
{
|
"#operator": "Parallel",
|
"~child": {
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "InitialProject",
|
"discard_original": true,
|
"distinct": true,
|
"optimizer_estimates": {
|
"cardinality": 0.4865828809674079,
|
"cost": 2974.4544180516446,
|
"fr_cost": 18.2724059076917,
|
"size": 113
|
},
|
"result_terms": [
|
{
|
"expr": "`v`",
|
"star": true
|
}
|
]
|
},
|
{
|
"#operator": "Distinct",
|
"optimizer_estimates": {
|
"cardinality": 0.4622537369190375,
|
"cost": 2974.459590498619,
|
"fr_cost": 18.277578354666364,
|
"size": 113
|
}
|
}
|
]
|
}
|
},
|
{
|
"#operator": "Distinct",
|
"optimizer_estimates": {
|
"cardinality": 0.4622537369190375,
|
"cost": 2974.459590498619,
|
"fr_cost": 18.277578354666364,
|
"size": 113
|
}
|
}
|
]
|
},
|
{
|
"#operator": "Limit",
|
"expr": "100",
|
"optimizer_estimates": {
|
"cardinality": 0.4622537369190375,
|
"cost": 2974.4702206444317,
|
"fr_cost": 18.288208500479097,
|
"size": 113
|
}
|
}
|
]
|
},
|
"text": "SELECT DISTINCT v.* FROM default AS d UNNEST d.documents AS v WHERE d.type = \"account\" AND d.accountId = \"1000\" AND v.status <> \"complete\" LIMIT 100 OFFSET 0;"
|
}
|
],
|
"status": "success",
|
"metrics": {
|
"elapsedTime": "11.910716ms",
|
"executionTime": "11.765368ms",
|
"resultCount": 1,
|
"resultSize": 10711,
|
"serviceLoad": 3
|
}
|
}
|
|
Note that IntersectScan is used, and the IntersectScan has a valid "optimizer_estimate" section.