[jira] [Created] (OAK-8488) Limiting entry count to query limit (maxEntryCount) leads to index traversing

["Piotr Tajduś (JIRA)" <ji...@apache.org>]

Piotr Tajduś created OAK-8488:
---------------------------------

             Summary: Limiting entry count to query limit (maxEntryCount) leads to index traversing
                 Key: OAK-8488
                 URL: https://issues.apache.org/jira/browse/OAK-8488
             Project: Jackrabbit Oak
          Issue Type: Bug
          Components: core
    Affects Versions: 1.14.0
            Reporter: Piotr Tajduś


There is line in QueryImpl.getBestSelectorExecutionPlan(NodeState, FilterImpl, QueryIndexProvider, boolean) limiting entryCount to query limit:
{code:java}
if (sortOrder == null || p.getSortOrder() != null) {
 // if the query is unordered, or
 // if the query contains "order by" and the index can sort on that,
 // then we don't need to read all entries from the index
 entryCount = Math.min(maxEntryCount, entryCount);
 }{code}
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.



--
This message was sent by Atlassian JIRA
(v7.6.14#76016)