Details
Major Description
So I've tried some combination on a data I've generated with indexes, MOI and some queries.
bucket people.
with that query everything is fine and working well:
select distinct age from `people`
|
where name = 'Roi' |
order by age
|
limit 10; |
|
|
[
|
{
|
"age": 15 |
},
|
{
|
"age": 16 |
},
|
{
|
"age": 17 |
},
|
{
|
"age": 18 |
},
|
{
|
"age": 19 |
},
|
{
|
"age": 20 |
},
|
{
|
"age": 21 |
},
|
{
|
"age": 22 |
},
|
{
|
"age": 23 |
},
|
{
|
"age": 24 |
}
|
]
|
explain:
[
|
{
|
"plan": { |
"#operator": "Sequence", |
"~children": [ |
{
|
"#operator": "Sequence", |
"~children": [ |
{
|
"#operator": "IndexScan", |
"covers": [ |
"cover ((`people`.`name`))", |
"cover ((`people`.`age`))", |
"cover ((meta(`people`).`id`))" |
],
|
"index": "idx_nameage", |
"index_id": "7ebdeb4b3bf0e807", |
"keyspace": "people", |
"namespace": "default", |
"spans": [ |
{
|
"Range": { |
"High": [ |
"successor(\"Roi\")" |
],
|
"Inclusion": 1, |
"Low": [ |
"\"Roi\"" |
]
|
}
|
}
|
],
|
"using": "gsi" |
},
|
{
|
"#operator": "Parallel", |
"~child": { |
"#operator": "Sequence", |
"~children": [ |
{
|
"#operator": "Filter", |
"condition": "(cover ((`people`.`name`)) = \"Roi\")" |
},
|
{
|
"#operator": "InitialProject", |
"distinct": true, |
"result_terms": [ |
{
|
"expr": "cover ((`people`.`age`))" |
}
|
]
|
},
|
{
|
"#operator": "Distinct" |
}
|
]
|
}
|
},
|
{
|
"#operator": "Distinct" |
}
|
]
|
},
|
{
|
"#operator": "Order", |
"limit": "10", |
"sort_terms": [ |
{
|
"expr": "cover ((`people`.`age`))" |
}
|
]
|
},
|
{
|
"#operator": "Limit", |
"expr": "10" |
},
|
{
|
"#operator": "FinalProject" |
}
|
]
|
},
|
"text": "select distinct age from `people` \nwhere name = 'Roi'\norder by age\nlimit 10;" |
}
|
]
|
But look what happen when I add "raw" keyword before it:
select distinct raw age from `people`
|
where name = 'Roi' |
order by age
|
limit 10; |
[
|
37, |
60, |
46, |
55, |
35, |
59, |
20, |
15, |
53, |
44 |
]
|
Explain:
[
|
{
|
"plan": { |
"#operator": "Sequence", |
"~children": [ |
{
|
"#operator": "Sequence", |
"~children": [ |
{
|
"#operator": "IndexScan", |
"covers": [ |
"cover ((`people`.`name`))", |
"cover ((`people`.`age`))", |
"cover ((meta(`people`).`id`))" |
],
|
"index": "idx_nameage", |
"index_id": "7ebdeb4b3bf0e807", |
"keyspace": "people", |
"namespace": "default", |
"spans": [ |
{
|
"Range": { |
"High": [ |
"successor(\"Roi\")" |
],
|
"Inclusion": 1, |
"Low": [ |
"\"Roi\"" |
]
|
}
|
}
|
],
|
"using": "gsi" |
},
|
{
|
"#operator": "Parallel", |
"~child": { |
"#operator": "Sequence", |
"~children": [ |
{
|
"#operator": "Filter", |
"condition": "(cover ((`people`.`name`)) = \"Roi\")" |
},
|
{
|
"#operator": "InitialProject", |
"distinct": true, |
"raw": true, |
"result_terms": [ |
{
|
"expr": "cover ((`people`.`age`))" |
}
|
]
|
},
|
{
|
"#operator": "Distinct" |
}
|
]
|
}
|
},
|
{
|
"#operator": "Distinct" |
}
|
]
|
},
|
{
|
"#operator": "Order", |
"limit": "10", |
"sort_terms": [ |
{
|
"expr": "cover ((`people`.`age`))" |
}
|
]
|
},
|
{
|
"#operator": "Limit", |
"expr": "10" |
},
|
{
|
"#operator": "FinalProject" |
}
|
]
|
},
|
"text": "select distinct raw age from `people` \nwhere name = 'Roi'\norder by age\nlimit 10;" |
}
|
]
|
and the index:
Definition: CREATE INDEX `idx_nameage` ON `people`(`name`,`age`)
And sample document:
{
|
"name": "Arik", |
"lastName": "Aviad", |
"note2": "blahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblah blah", |
"city": "Tel Aviv", |
"note1": "blahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblah blah", |
"age": 50 |
}
|
I don't know the criticality level so I set it to Major.
Attachments
| For Gerrit Dashboard: MB-19893 | ||||||
|---|---|---|---|---|---|---|
| # | Subject | Branch | Project | Status | CR | V |
| 64859,2 | MB-19893 Avoid pushing order to indexer when projection has Distinct | master | query | Status: MERGED | +2 | +1 |