Description
DROP INDEX default.ix1;
|
DROP INDEX default.ix2;
|
CREATE INDEX ix1 ON default(c1, DISTINCT ARRAY FLATTEN_KEYS(v.ac1, v.ac2.type) FOR v IN a1 END, c3);
|
CREATE INDEX ix2 ON default(c1, DISTINCT ARRAY FLATTEN_KEYS(v.ac1, v.ac2, v.ac3) FOR v IN a1 END, c3);
|
EXPLAIN SELECT c3
|
FROM default
|
WHERE c1 = 5
|
AND ANY v IN a1 SATISFIES v.ac1 = 5 AND v.ac2.type >= 10 END
|
AND ANY v IN a1 SATISFIES v.ac1 = 6 AND v.ac2 = 12 AND v.ac3 >= 30 END
|
ORDER BY c3
|
LIMIT 10;
|
{
|
"requestID": "f9af9037-eb7e-435e-a03e-5186af57ebcc",
|
"signature": "json",
|
"results": [
|
{
|
"plan": {
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "IntersectScan",
|
"scans": [
|
{
|
"#operator": "DistinctScan",
|
"scan": {
|
"#operator": "IndexScan3",
|
"index": "ix2",
|
"index_id": "b3b008c3a65019a",
|
"index_projection": {
|
"primary_key": true
|
},
|
"keyspace": "default",
|
"namespace": "default",
|
"optimizer_estimates": {
|
"cardinality": 1.0842021724855044e-19,
|
"cost": 8.093569217604291e-18,
|
"fr_cost": 8.093569217604291e-18,
|
"size": 9
|
},
|
"spans": [
|
{
|
"exact": true,
|
"range": [
|
{
|
"high": "5",
|
"inclusion": 3,
|
"index_key": "`c1`",
|
"low": "5"
|
},
|
{
|
"high": "5",
|
"inclusion": 3,
|
"index_key": "(`v`.`ac1`)",
|
"low": "5"
|
},
|
{
|
"inclusion": 0,
|
"index_key": "(`v`.`ac2`)",
|
"low": "null"
|
},
|
{
|
"inclusion": 0,
|
"index_key": "(`v`.`ac3`)"
|
}
|
]
|
}
|
],
|
"using": "gsi"
|
}
|
},
|
{
|
"#operator": "DistinctScan",
|
"scan": {
|
"#operator": "IndexScan3",
|
"index": "ix1",
|
"index_id": "f9c872cc2a961bc7",
|
"index_projection": {
|
"primary_key": true
|
},
|
"keyspace": "default",
|
"namespace": "default",
|
"optimizer_estimates": {
|
"cardinality": 1.0842021724855044e-19,
|
"cost": 8.093569217604291e-18,
|
"fr_cost": 8.093569217604291e-18,
|
"size": 9
|
},
|
"spans": [
|
{
|
"exact": true,
|
"range": [
|
{
|
"high": "5",
|
"inclusion": 3,
|
"index_key": "`c1`",
|
"low": "5"
|
},
|
{
|
"high": "5",
|
"inclusion": 3,
|
"index_key": "(`v`.`ac1`)",
|
"low": "5"
|
},
|
{
|
"inclusion": 1,
|
"index_key": "((`v`.`ac2`).`type`)",
|
"low": "10"
|
}
|
]
|
}
|
],
|
"using": "gsi"
|
}
|
}
|
]
|
},
|
{
|
"#operator": "Fetch",
|
"early_projection": [
|
"a1",
|
"c1",
|
"c3"
|
],
|
"keyspace": "default",
|
"namespace": "default"
|
},
|
{
|
"#operator": "Parallel",
|
"~child": {
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "Filter",
|
"condition": "((((`default`.`c1`) = 5) and any `v` in (`default`.`a1`) satisfies (((`v`.`ac1`) = 5) and (10 \u003c= ((`v`.`ac2`).`type`))) end) and any `v` in (`default`.`a1`) satisfies ((((`v`.`ac1`) = 6) and ((`v`.`ac2`) = 12)) and (30 \u003c= (`v`.`ac3`))) end)"
|
},
|
{
|
"#operator": "InitialProject",
|
"preserve_order": true,
|
"result_terms": [
|
{
|
"expr": "(`default`.`c3`)"
|
}
|
]
|
}
|
]
|
}
|
}
|
]
|
},
|
{
|
"#operator": "Order",
|
"flags": 6,
|
"limit": "10",
|
"sort_terms": [
|
{
|
"expr": "(`default`.`c3`)"
|
}
|
]
|
},
|
{
|
"#operator": "Limit",
|
"expr": "10"
|
}
|
]
|
},
|
"text": "SELECT c3 FROM default WHERE c1 = 5 AND ANY v IN a1 SATISFIES v.ac1 = 5 AND v.ac2.type \u003e= 10 END AND ANY v IN a1 SATISFIES v.ac1 = 6 AND v.ac2 = 12 AND v.ac3 \u003e= 30 END ORDER BY c3 LIMIT 10;"
|
}
|
],
|
"status": "success",
|
"metrics": {
|
"elapsedTime": "3.955666ms",
|
"executionTime": "3.8355ms",
|
"resultCount": 1,
|
"resultSize": 8277,
|
"serviceLoad": 2
|
}
|
}
|