Description
CREATE INDEX `idx_country` ON `default`(`address`.`country`) WHERE (`type` = "user"); |
CREATE INDEX `gix_USMales` ON `default`(distinct (pairs(self))) WHERE (`type` = "user") and (`gender` = "M") and (`address`.`country` = "United States of America"); |
|
|
idx_country is not covered, so filters can't use covers.
|
|
|
Below is the output we used to get before resolving this bug: |
explain select count(*) from default where type = 'user' and address.country = 'United States of America' and gender = 'M'; |
{
|
"requestID": "1b38116d-94c9-4d3a-bf7b-cd4de4007066", |
"signature": "json", |
"results": [ |
{
|
"plan": { |
"#operator": "Sequence", |
"~children": [ |
{
|
"#operator": "IntersectScan", |
"scans": [ |
{
|
"#operator": "IndexScan2", |
"index": "idx_country", |
"index_id": "c9b5e2b0ad17028", |
"index_projection": { |
"primary_key": true |
},
|
"keyspace": "default", |
"namespace": "default", |
"spans": [ |
{
|
"exact": true, |
"range": [ |
{
|
"high": "\"United States of America\"", |
"inclusion": 3, |
"low": "\"United States of America\"" |
}
|
]
|
}
|
],
|
"using": "gsi" |
},
|
{
|
"#operator": "IndexScan2", |
"covers": [ |
"cover ((distinct (pairs(`default`))))", |
"cover ((meta(`default`).`id`))" |
],
|
"filter_covers": { |
"cover (((`default`.`address`).`country`))": "United States of America", |
"cover ((`default`.`gender`))": "M", |
"cover ((`default`.`type`))": "user", |
"cover (any `p` in pairs(`default`) satisfies (`p` = [\"country\", \"United States of America\"]) end)": true, |
"cover (any `p` in pairs(`default`) satisfies (`p` = [\"gender\", \"M\"]) end)": true, |
"cover (any `p` in pairs(`default`) satisfies (`p` = [\"type\", \"user\"]) end)": true |
},
|
"index": "gix_USMales", |
"index_id": "3e8661a45258dc17", |
"index_projection": { |
"primary_key": true |
},
|
"keyspace": "default", |
"namespace": "default", |
"spans": [ |
{
|
"exact": true, |
"range": [ |
{
|
"high": "[\"type\", \"user\"]", |
"inclusion": 3, |
"low": "[\"type\", \"user\"]" |
}
|
]
|
}
|
],
|
"using": "gsi" |
},
|
{
|
"#operator": "IndexScan2", |
"covers": [ |
"cover ((distinct (pairs(`default`))))", |
"cover ((meta(`default`).`id`))" |
],
|
"filter_covers": { |
"cover (((`default`.`address`).`country`))": "United States of America", |
"cover ((`default`.`gender`))": "M", |
"cover ((`default`.`type`))": "user", |
"cover (any `p` in pairs(`default`) satisfies (`p` = [\"country\", \"United States of America\"]) end)": true, |
"cover (any `p` in pairs(`default`) satisfies (`p` = [\"gender\", \"M\"]) end)": true, |
"cover (any `p` in pairs(`default`) satisfies (`p` = [\"type\", \"user\"]) end)": true |
},
|
"index": "gix_USMales", |
"index_id": "3e8661a45258dc17", |
"index_projection": { |
"primary_key": true |
},
|
"keyspace": "default", |
"namespace": "default", |
"spans": [ |
{
|
"exact": true, |
"range": [ |
{
|
"high": "[\"country\", \"United States of America\"]", |
"inclusion": 3, |
"low": "[\"country\", \"United States of America\"]" |
}
|
]
|
}
|
],
|
"using": "gsi" |
},
|
{
|
"#operator": "IndexScan2", |
"covers": [ |
"cover ((distinct (pairs(`default`))))", |
"cover ((meta(`default`).`id`))" |
],
|
"filter_covers": { |
"cover (((`default`.`address`).`country`))": "United States of America", |
"cover ((`default`.`gender`))": "M", |
"cover ((`default`.`type`))": "user", |
"cover (any `p` in pairs(`default`) satisfies (`p` = [\"country\", \"United States of America\"]) end)": true, |
"cover (any `p` in pairs(`default`) satisfies (`p` = [\"gender\", \"M\"]) end)": true, |
"cover (any `p` in pairs(`default`) satisfies (`p` = [\"type\", \"user\"]) end)": true |
},
|
"index": "gix_USMales", |
"index_id": "3e8661a45258dc17", |
"index_projection": { |
"primary_key": true |
},
|
"keyspace": "default", |
"namespace": "default", |
"spans": [ |
{
|
"exact": true, |
"range": [ |
{
|
"high": "[\"gender\", \"M\"]", |
"inclusion": 3, |
"low": "[\"gender\", \"M\"]" |
}
|
]
|
}
|
],
|
"using": "gsi" |
}
|
]
|
},
|
{
|
"#operator": "Parallel", |
"~child": { |
"#operator": "Sequence", |
"~children": [ |
{
|
"#operator": "Filter", |
"condition": "(((cover ((`default`.`type`)) = \"user\") and (cover (((`default`.`address`).`country`)) = \"United States of America\")) and (cover ((`default`.`gender`)) = \"M\"))" |
},
|
{
|
"#operator": "InitialGroup", |
"aggregates": [ |
"count(*)" |
],
|
"group_keys": [] |
}
|
]
|
}
|
},
|
{
|
"#operator": "IntermediateGroup", |
"aggregates": [ |
"count(*)" |
],
|
"group_keys": [] |
},
|
{
|
"#operator": "FinalGroup", |
"aggregates": [ |
"count(*)" |
],
|
"group_keys": [] |
},
|
{
|
"#operator": "Parallel", |
"~child": { |
"#operator": "Sequence", |
"~children": [ |
{
|
"#operator": "InitialProject", |
"result_terms": [ |
{
|
"expr": "count(*)" |
}
|
]
|
},
|
{
|
"#operator": "FinalProject" |
}
|
]
|
}
|
}
|
]
|
},
|
"text": "select count(*) from default where type = 'user' and address.country = 'United States of America' and gender = 'M';" |
}
|
],
|
"status": "success", |
"metrics": { |
"elapsedTime": "44.417604ms", |
"executionTime": "44.39548ms", |
"resultCount": 1, |
"resultSize": 10246 |
}
|
}
|
Attachments
Issue Links
- blocks
-
MB-22188 4.6.2 Minor Release
-
- Closed
-