Details
-
Bug
-
Resolution: Fixed
-
Major
-
7.6.2
-
7.6.2-3570
-
0
Description
To repro:
- create default bucket
- CREATE INDEX ix1 ON default(id) WHERE type = "type1";
- CREATE INDEX ix2 ON default(id, type) WHERE type NOT IN ["type2", "type3"];
- insert into default (key k, value v) select uuid() as k , {"name": "San Francisco", "id": d, "type": "type1"} as v from array_range(1,500) d ;
- insert into default (key k, value v) select uuid() as k , {"name": "San Francisco", "id": d, "type": "type2"} as v from array_range(500,1500) d ;
- insert into default (key k, value v) select uuid() as k , {"name": "San Francisco", "id": d, "type": "type4"} as v from array_range(1500,2500) d ;
- update statistics for default(id,type);
Following query should pick ix1 but seems to prefer ix2
cbq> explain SELECT * FROM default WHERE type = "type1" AND id = $my_id; |
{
|
"requestID": "b0981366-716f-4d8f-b2ab-9592aa59e70b", |
"signature": "json", |
"results": [ |
{
|
"cardinality": 29.932012805122053, |
"cost": 78.64422026926641, |
"plan": { |
"#operator": "Sequence", |
"~children": [ |
{
|
"#operator": "IndexScan3", |
"index": "ix2", |
"index_id": "8280c8394b51977b", |
"index_projection": { |
"primary_key": true |
},
|
"keyspace": "default", |
"namespace": "default", |
"optimizer_estimates": { |
"cardinality": 29.932012805122053, |
"cost": 21.226429542302334, |
"fr_cost": 12.308246211251236, |
"size": 36 |
},
|
"spans": [ |
{
|
"exact": true, |
"range": [ |
{
|
"high": "$my_id", |
"inclusion": 3, |
"index_key": "`id`", |
"low": "$my_id" |
},
|
{
|
"high": "\"type1\"", |
"inclusion": 3, |
"index_key": "`type`", |
"low": "\"type1\"" |
}
|
]
|
}
|
],
|
"using": "gsi" |
},
|
{
|
"#operator": "Fetch", |
"keyspace": "default", |
"namespace": "default", |
"optimizer_estimates": { |
"cardinality": 29.932012805122053, |
"cost": 77.55673623924707, |
"fr_cost": 25.789279477726346, |
"size": 330 |
}
|
},
|
{
|
"#operator": "Parallel", |
"~child": { |
"#operator": "Sequence", |
"~children": [ |
{
|
"#operator": "Filter", |
"condition": "(((`default`.`type`) = \"type1\") and ((`default`.`id`) = $my_id))", |
"optimizer_estimates": { |
"cardinality": 29.932012805122053, |
"cost": 78.10047825425674, |
"fr_cost": 25.807445379850932, |
"size": 330 |
}
|
},
|
{
|
"#operator": "InitialProject", |
"discard_original": true, |
"optimizer_estimates": { |
"cardinality": 29.932012805122053, |
"cost": 78.64422026926641, |
"fr_cost": 25.82561128197552, |
"size": 330 |
},
|
"preserve_order": true, |
"result_terms": [ |
{
|
"expr": "self", |
"star": true |
}
|
]
|
}
|
]
|
}
|
}
|
]
|
},
|
"text": "SELECT * FROM default WHERE type = \"type1\" AND id = $my_id;" |
}
|
],
|
"status": "success", |
"metrics": { |
"elapsedTime": "1.670591ms", |
"executionTime": "1.576737ms", |
"resultCount": 1, |
"resultSize": 3837, |
"serviceLoad": 6 |
}
|
}
|
and cost for ix1 is lower here:
cbq> explain SELECT * FROM default USE INDEX(ix1) WHERE type = "type1" AND id = $my_id; |
{
|
"requestID": "65b846f6-aa99-4cb3-a749-046ac65e79d8", |
"signature": "json", |
"results": [ |
{
|
"cardinality": 49.900000000000006, |
"cost": 115.07304076709308, |
"optimizer_hints": { |
"hints_followed": [ |
"INDEX(default ix1)" |
]
|
},
|
"plan": { |
"#operator": "Sequence", |
"~children": [ |
{
|
"#operator": "IndexScan3", |
"index": "ix1", |
"index_id": "41a983e582aee6ec", |
"index_projection": { |
"primary_key": true |
},
|
"keyspace": "default", |
"namespace": "default", |
"optimizer_estimates": { |
"cardinality": 49.900000000000006, |
"cost": 27.356523737951502, |
"fr_cost": 12.307745966692416, |
"size": 36 |
},
|
dictionary info for each index:
cbq> select * from system:dictionary;
|
{
|
"requestID": "26835bc5-0744-4994-b49e-75d3f887b92f", |
"signature": { |
"*": "*" |
},
|
"results": [ |
{
|
"dictionary": { |
"avgDocKeySize": 36, |
"avgDocSize": 294, |
"bucket": "default", |
"distributionKeys": [ |
"id", |
"type", |
"(meta().id)" |
],
|
"docCount": 2499, |
"indexes": [ |
{
|
"indexId": "41a983e582aee6ec", |
"indexName": "ix1", |
"indexStats": [ |
{
|
"avgItemSize": 60, |
"avgPageSize": 12211, |
"numItems": 499, |
"numPages": 8, |
"resRatio": 1 |
}
|
]
|
},
|
{
|
"indexId": "8280c8394b51977b", |
"indexName": "ix2", |
"indexStats": [ |
{
|
"avgItemSize": 68, |
"avgPageSize": 15581, |
"numItems": 1499, |
"numPages": 13, |
"resRatio": 1 |
}
|
]
|
}
|
],
|
"keyspace": "_default", |
"namespace": "default", |
"resident": 0.47453003222014195, |
"scope": "_default", |
"updated": "2024-04-23T14:24:08.099-07:00" |
}
|
}
|
],
|
"status": "success", |
"metrics": { |
"elapsedTime": "7.284011ms", |
"executionTime": "7.123118ms", |
"resultCount": 1, |
"resultSize": 1469, |
"serviceLoad": 6 |
}
|
}
|
Attachments
| For Gerrit Dashboard: MB-61620 | ||||||
|---|---|---|---|---|---|---|
| # | Subject | Branch | Project | Status | CR | V |
| 209096,2 | MB-61620 Proper calculation of index key selectivity | trinity | query-ee | Status: MERGED | +2 | +1 |
| 209111,2 | MB-61620 Revision of fix | trinity | query-ee | Status: MERGED | +2 | +1 |
| 209115,2 | MB-61620 Proper calculation of index key selectivity | neo | query-ee | Status: MERGED | +2 | +1 |