Affects Version/s: 1.14.0
Fix Version/s: None
There is line in QueryImpl.getBestSelectorExecutionPlan(NodeState, FilterImpl, QueryIndexProvider, boolean) limiting entryCount to query limit:
In some cases it leads to index traversing through many records. Example:
Lets say we have over million documents. Document has property "system" which has 3 unique values (S1, S2 and S3), two properties with id - "ID1" and "ID2" which are unique, and creation date "created", all are indexed. System has low weight and boost, ids have high weight and boost. Now we are executing select:
select * from document where system='S1' and (ID1='1234' or ID2='1234') order by created desc
limit = 100, offset = 0
First plan is to search index only with property "system", lets say cost is 300000, but we are cutting it to 100.
Next plans are from alternative query with union, every query has cost 30000, its also limited to 100.
Every plan has the same cost, so in the end first plan is selected. Instead of quick query with ID we are index traversing 300000 documents.