Details
-
Bug
-
Resolution: Fixed
-
Major
-
7.2.1
-
Untriaged
-
0
-
Yes
Description
create index ix20 on default(sp_low, sp_high);
explain SELECT 1 FROM default AS d WHERE ( d.sp_low <= "u1wc9" AND d.sp_high >= "u1wc8" ) OR ( d.sp_low <= "u1x1f" AND d.sp_high >= "u1x1e" ) limit 1;
|
{
|
"requestID": "1fbd5aa3-a4d1-4330-8866-31aba8efdb36",
|
"signature": "json",
|
"results": [
|
{
|
"plan": {
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "IndexScan3",
|
"as": "d",
|
"covers": [
|
"cover ((`d`.`sp_low`))",
|
"cover ((`d`.`sp_high`))",
|
"cover ((meta(`d`).`id`))"
|
],
|
"index": "ix20",
|
"index_id": "c44b9efdf94924e8",
|
"index_projection": {
|
"entry_keys": [
|
0,
|
1
|
]
|
},
|
"keyspace": "default",
|
"limit": "1",
|
"namespace": "default",
|
"spans": [
|
{
|
"exact": true,
|
"range": [
|
{
|
"high": "\"u1wc9\"",
|
"inclusion": 2,
|
"index_key": "`sp_low`",
|
"low": "null"
|
},
|
{
|
"inclusion": 1,
|
"index_key": "`sp_high`",
|
"low": "\"u1wc8\""
|
}
|
]
|
},
|
{
|
"exact": true,
|
"range": [
|
{
|
"high": "\"u1wc9\"",
|
"inclusion": 2,
|
"index_key": "`sp_low`",
|
"low": "null"
|
},
|
{
|
"inclusion": 1,
|
"index_key": "`sp_high`",
|
"low": "\"u1x1e\""
|
}
|
]
|
},
|
{
|
"exact": true,
|
"range": [
|
{
|
"high": "\"u1x1f\"",
|
"inclusion": 2,
|
"index_key": "`sp_low`",
|
"low": "null"
|
},
|
{
|
"inclusion": 1,
|
"index_key": "`sp_high`",
|
"low": "\"u1wc8\""
|
}
|
]
|
},
|
{
|
"exact": true,
|
"range": [
|
{
|
"high": "\"u1x1f\"",
|
"inclusion": 2,
|
"index_key": "`sp_low`",
|
"low": "null"
|
},
|
{
|
"inclusion": 1,
|
"index_key": "`sp_high`",
|
"low": "\"u1x1e\""
|
}
|
]
|
}
|
],
|
"using": "gsi"
|
},
|
{
|
"#operator": "Parallel",
|
"~child": {
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "Filter",
|
"condition": "(((cover ((`d`.`sp_low`)) <= \"u1wc9\") and (\"u1wc8\" <= cover ((`d`.`sp_high`)))) or ((cover ((`d`.`sp_low`)) <= \"u1x1f\") and (\"u1x1e\" <= cover ((`d`.`sp_high`)))))"
|
},
|
{
|
"#operator": "InitialProject",
|
"discard_original": true,
|
"result_terms": [
|
{
|
"expr": "1"
|
}
|
]
|
}
|
]
|
}
|
}
|
]
|
},
|
{
|
"#operator": "Limit",
|
"expr": "1"
|
}
|
]
|
},
|
"text": "SELECT 1 FROM default AS d WHERE ( d.sp_low <= \"u1wc9\" AND d.sp_high >= \"u1wc8\" ) OR ( d.sp_low <= \"u1x1f\" AND d.sp_high >= \"u1x1e\" ) limit 1;"
|
}
|
],
|
"status": "success",
|
"metrics": {
|
"elapsedTime": "2.285694ms",
|
"executionTime": "2.170461ms",
|
"resultCount": 1,
|
"resultSize": 5948,
|
"serviceLoad": 3
|
}
|
}
|
Why this query has 4 spans vs 2 spans? It is already DNF transformed.
If 4 spans false positives possible and shouldn't have pushed limit.
cc Bingjie Miao
Attachments
Issue Links
- is triggered by
-
MB-51817 Push filters to index scan to potentially reduce fetch size
- Resolved
For Gerrit Dashboard: MB-58150 | ||||||
---|---|---|---|---|---|---|
# | Subject | Branch | Project | Status | CR | V |
195012,3 | MB-58150 Further limit cases we use OR clause filters for sarging index keys | master | query | Status: MERGED | +2 | +1 |
196748,2 | MB-58150 Further limit cases we use OR clause filters for sarging index keys | neo | query | Status: MERGED | +2 | +1 |