Major Description
Running the following:
Select name from default where any v in default.join_yr satisfies v = 2016 END AND (ANY x IN default.VMs SATISFIES x.RAM between 1 and 5 END) AND (department != 'Manager') ORDER BY name limit 10 |
With indexes:
CREATE INDEX `idxjoin_yr` ON `default`((distinct (array `v` for `v` in `join_yr` end))) |
|
|
CREATE INDEX `idxVM` ON `default`((distinct (array (`x`.`RAM`) for `x` in `VMs` end))) |
|
|
CREATE PRIMARY INDEX `#primary` ON `default` |
|
Explain plan will be one of two plans:
Plan 1 (the plan that it should be):
{
|
"plan": {
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "IntersectScan",
|
"scans": [
|
{
|
"#operator": "DistinctScan",
|
"scan": {
|
"#operator": "IndexScan3",
|
"index": "idxjoin_yr",
|
"index_id": "eb318b447ec1ff5d",
|
"index_projection": {
|
"primary_key": true
|
},
|
"keyspace": "default",
|
"namespace": "default",
|
"spans": [
|
{
|
"exact": true,
|
"range": [
|
{
|
"high": "2016",
|
"inclusion": 3,
|
"low": "2016"
|
}
|
]
|
}
|
],
|
"using": "gsi"
|
}
|
},
|
{
|
"#operator": "DistinctScan",
|
"scan": {
|
"#operator": "IndexScan3",
|
"index": "idxVM",
|
"index_id": "a58d4c2e48904ba6",
|
"index_projection": {
|
"primary_key": true
|
},
|
"keyspace": "default",
|
"namespace": "default",
|
"spans": [
|
{
|
"exact": true,
|
"range": [
|
{
|
"high": "5",
|
"inclusion": 3,
|
"low": "1"
|
}
|
]
|
}
|
],
|
"using": "gsi"
|
}
|
}
|
]
|
},
|
{
|
"#operator": "Fetch",
|
"keyspace": "default",
|
"namespace": "default"
|
},
|
{
|
"#operator": "Parallel",
|
"~child": {
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "Filter",
|
"condition": "((any `v` in (`default`.`join_yr`) satisfies (`v` = 2016) end and any `x` in (`default`.`VMs`) satisfies ((`x`.`RAM`) between 1 and 5) end) and (not ((`default`.`department`) = \"Manager\")))"
|
},
|
{
|
"#operator": "InitialProject",
|
"result_terms": [
|
{
|
"expr": "(`default`.`name`)"
|
}
|
]
|
}
|
]
|
}
|
}
|
]
|
},
|
{
|
"#operator": "Order",
|
"limit": "10",
|
"sort_terms": [
|
{
|
"expr": "(`default`.`name`)"
|
}
|
]
|
},
|
{
|
"#operator": "Limit",
|
"expr": "10"
|
},
|
{
|
"#operator": "FinalProject"
|
}
|
]
|
},
|
"text": "select name from default where any v in default.join_yr satisfies v = 2016 END AND (ANY x IN default.VMs SATISFIES x.RAM between 1 and 5 END) AND (department != 'Manager') ORDER BY name limit 10"
|
}
|
|
Plan 2 has two of the exact same scans and only occurs every 4 or 5 tries:
{
|
"plan": {
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "UnionScan",
|
"scans": [
|
{
|
"#operator": "IntersectScan",
|
"scans": [
|
{
|
"#operator": "DistinctScan",
|
"scan": {
|
"#operator": "IndexScan3",
|
"index": "idxVM",
|
"index_id": "a58d4c2e48904ba6",
|
"index_projection": {
|
"primary_key": true
|
},
|
"keyspace": "default",
|
"namespace": "default",
|
"spans": [
|
{
|
"exact": true,
|
"range": [
|
{
|
"high": "5",
|
"inclusion": 3,
|
"low": "1"
|
}
|
]
|
}
|
],
|
"using": "gsi"
|
}
|
},
|
{
|
"#operator": "DistinctScan",
|
"scan": {
|
"#operator": "IndexScan3",
|
"index": "idxjoin_yr",
|
"index_id": "eb318b447ec1ff5d",
|
"index_projection": {
|
"primary_key": true
|
},
|
"keyspace": "default",
|
"namespace": "default",
|
"spans": [
|
{
|
"exact": true,
|
"range": [
|
{
|
"high": "2016",
|
"inclusion": 3,
|
"low": "2016"
|
}
|
]
|
}
|
],
|
"using": "gsi"
|
}
|
}
|
]
|
},
|
{
|
"#operator": "IntersectScan",
|
"scans": [
|
{
|
"#operator": "DistinctScan",
|
"scan": {
|
"#operator": "IndexScan3",
|
"index": "idxjoin_yr",
|
"index_id": "eb318b447ec1ff5d",
|
"index_projection": {
|
"primary_key": true
|
},
|
"keyspace": "default",
|
"namespace": "default",
|
"spans": [
|
{
|
"exact": true,
|
"range": [
|
{
|
"high": "2016",
|
"inclusion": 3,
|
"low": "2016"
|
}
|
]
|
}
|
],
|
"using": "gsi"
|
}
|
},
|
{
|
"#operator": "DistinctScan",
|
"scan": {
|
"#operator": "IndexScan3",
|
"index": "idxVM",
|
"index_id": "a58d4c2e48904ba6",
|
"index_projection": {
|
"primary_key": true
|
},
|
"keyspace": "default",
|
"namespace": "default",
|
"spans": [
|
{
|
"exact": true,
|
"range": [
|
{
|
"high": "5",
|
"inclusion": 3,
|
"low": "1"
|
}
|
]
|
}
|
],
|
"using": "gsi"
|
}
|
}
|
]
|
}
|
]
|
},
|
{
|
"#operator": "Fetch",
|
"keyspace": "default",
|
"namespace": "default"
|
},
|
{
|
"#operator": "Parallel",
|
"~child": {
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "Filter",
|
"condition": "((any `v` in (`default`.`join_yr`) satisfies (`v` = 2016) end and any `x` in (`default`.`VMs`) satisfies ((`x`.`RAM`) between 1 and 5) end) and (not ((`default`.`department`) = \"Manager\")))"
|
},
|
{
|
"#operator": "InitialProject",
|
"result_terms": [
|
{
|
"expr": "(`default`.`name`)"
|
}
|
]
|
}
|
]
|
}
|
}
|
]
|
},
|
{
|
"#operator": "Order",
|
"limit": "10",
|
"sort_terms": [
|
{
|
"expr": "(`default`.`name`)"
|
}
|
]
|
},
|
{
|
"#operator": "Limit",
|
"expr": "10"
|
},
|
{
|
"#operator": "FinalProject"
|
}
|
]
|
},
|
"text": "select name from default where any v in default.join_yr satisfies v = 2016 END AND (ANY x IN default.VMs SATISFIES x.RAM between 1 and 5 END) AND (department != 'Manager') ORDER BY name limit 10"
|
}
|
Attachments
| For Gerrit Dashboard: MB-28587 | ||||||
|---|---|---|---|---|---|---|
| # | Subject | Branch | Project | Status | CR | V |
| 193282,2 | MB-28587 Better compare scan hierarchies when planning | master | query | Status: MERGED | +2 | +1 |