Issue #2379: combined index not used

we have a query like this:

SELECT a.endDate,a.objectsInCharge.nodePath,a.objectsInCharge.properties.doubleValue
FROM Action a
WHERE a.type = 0 and a.subType = 3 and a.state = 3 and a.endDate >= ?1 and
     (a.objectsInCharge.linkedObjects.classIdentifier = '(TC)' and
      a.objectsInCharge.linkedObjects.objectNameUpper = 'SETUP' and
      a.objectsInCharge.linkedObjects.type = 1) and
     (a.objectsInCharge.properties.name = 'scheduledSlot')
GROUP BY a.endDate, a.objectsInCharge.nodePath, a.objectsInCharge.properties.doubleValue

In the class Action we have a combined index (see attachment) but in the query plan we can see that this index is not used:

Query Plan Description
======================

Step 1: Process Action (a) instances
------------------------------------

[Step 1a]
Scan index com.agile.hummingbird.Action[endDate]
locating all the Action (a) instances.

[Step 1b]
Filter the results of step 1a
retaining only results that satisfy:
  (a.endDate>=:1).

[Step 1c]
Retrieve fields in Action (a) instances.

[Step 1d]
Filter the results of step 1c
retaining only results that satisfy:
  (a.state=3).

[Step 1e]
Filter the results of step 1d
retaining only results that satisfy:
  (a.subType=3).

[Step 1f]
Filter the results of step 1e
retaining only results that satisfy:
  (a.type=0).

Step 2: Process ObjectNode (v$1) instances
  (for every result of step 1)
------------------------------------------

Iterate over all the instances (v$1) in a.objectsInCharge.

Step 3: Extract field values
----------------------------

Retrieve fields in ObjectNode (v$1) instances.

Step 4: Process ObjectNode (v$2) instances
  (for every result tuple of the steps above)
------------------------------------------

[Step 4a]
Scan index com.agile.hummingbird.ObjectNode[classIdentifier, type, state, objectNameUpper]
locating ObjectNode (v$2) instances that satisfy:
  and(and((v$2.objectNameUpper='SETUP'),(v$2.classIdentifier='(TC)')),(v$2.type=1)).

[Step 4b]
Filter the results of step 4a
retaining only results that satisfy:
  (v$1.linkedObjects join v$2).

Step 5: Process ObjectProperty (v$3) instances
  (for every result tuple of the steps above)
----------------------------------------------

[Step 5a]
Scan type com.agile.hummingbird.ObjectProperty
locating ObjectProperty (v$3) instances that satisfy:
  (v$1.properties join v$3).

[Step 5b]
Evaluate fields in ObjectProperty (v$3) instances.

[Step 5c]
Filter the results of step 5b
retaining only results that satisfy:
  (v$3.name='scheduledSlot').

Step 6: Group results
---------------------

Group the results of step 5 by:
  a.endDate,v$1.nodePath,v$3.doubleValue.

Step 7: Apply selection
-----------------------

Apply selection and prepare final results.

Running the query on the sample database that you sent in the Explorer shows that it is indeed very slow.

Could you please provide a typical value for the ?1 (end date) parameter (on that sample database) for further analysis?

we do not have that database any more, but you could check in the Action table, typically we take an endDate that is 30 days from the current date (in your case the date of the last Action). The endDate is created as soon as an Action switches to state = 2 or state = 3

The query planner considered the composite index but eventually selected another query plan. We will try to understand that selection and provide a fix.

The reason for not using the composite index was found and can be fixed.  As there are too many possible combinations for covering the filter: "a.type = 0 and a.subType = 3 and a.state = 3 and a.endDate >= ?1" with indexes (each field is covered by many indexes and there were about 5,000 total combinations), ObjectDB did not evaluate all of them and missed the best combination of using the composite index on these 4 fields. Increasing the threshold from 1024 combinations to 10000 solves this issue so a quick fix is possible for this specific issue (although a better approach will be needed for a permanent solution).

However, this query is still extremely slow as using this composite index doesn't solve the problem, so further work is needed. Will keep you informed.

As discussed above, the reason that this query is very slow is not because of not using the composite index (although using the composite index is preferred and can improve performance).

Complex heavy queries with several JOIN operations require examining large number of options for executing the query. The query planner examines thousands of options eliminating options that are clearly inferior to other available options. Eventually a short list of query plans that represent different approaches for executing the query is prepared, each query plan is evaluated and the best evaluated query plan is selected.

The problem is that the evaluation of a query plan without executing it is guessing. Think of the progress bar and the evaluation of time remained when you copy directories with many files in Windows, which is very inaccurate. Evaluating query plans is much more complex and could easily be inaccurate. Future version of ObjectDB will probably have to try different query plans by running them in the background in order to optimise and select best query plans better.

The following query was examined:

SELECT a.endDate,a.objectsInCharge.nodePath,a.objectsInCharge.properties.doubleValue
FROM Action a
WHERE a.type = 0 and a.subType = 3 and a.state = 3 and a.endDate >= {d '2018-07-20'} and
     (a.objectsInCharge.linkedObjects.classIdentifier = '(TC)' and
      a.objectsInCharge.linkedObjects.objectNameUpper = 'SETUP' and
      a.objectsInCharge.linkedObjects.type = 1) and
     (a.objectsInCharge.properties.name = 'scheduledSlot')
GROUP BY a.endDate, a.objectsInCharge.nodePath, a.objectsInCharge.properties.doubleValue

on a database with actions until 2018-07-25.

8 query plans are included in the final short list of candidate query plans. The following list shows the actual execution time of each query plan from the best evaluated to the worst evaluated (after fixing the "composite index not used" issue):

Plan 1 - 339 results 88446 ms
Plan 2 - 339 results 2827 ms
Plan 3 - 339 results 31764 ms
Plan 4 - 339 results 2913 ms
Plan 5 - 339 results 52364 ms
Plan 6 - 339 results 22174 ms
Plan 7 - 339 results 51636 ms
Plan 8 - 339 results 22524 ms

Plans 2 and 4 are clearly much faster than the other query plans. However due to an incorrect evaluation of plan 1 it takes the first place in the evaluation contest and selected.

This issue requires further analysis and work, but in order to offer a quick solution ObjectDB 2.7.6_01 was now released, with:

• Increased threshold of examining combination, which fixes the "composite index not used" and a new warning to the log if this threshold is exceeded again.
• Ability to manually select the query plan that is executed using a hint (will be explained in the next post).

As you have several heavy queries with multiple JOIN operations with very slow execution time you may be able to manually select the best query plans for these queries as a quick workaround.

To manually select a query plan 2 new query hints are introduced in build 2.7.6_01:

1. Selecting a query plan from the sorted list of query plans by position:

    query.setHint("objectdb.query-plan-pos", 2);

This tells ObjectDB to skip the first query plan in the list.

2. Selecting a query plan by a specific required text in its XML representation:

    query.setHint("objectdb.query-plan-text", "ObjectNode[set]");

This tells ObjectDB to prefer a query plan that uses the "ObjectNode[set]" element (included in the query plan XML representation).  In the case of the query from the post above it will select the 2nd query plan. Selecting a query plan by text (e.g. specific index or component that has to be used) may be more stable and less affected by small changes in the query or in ObjectDB.

The Explorer includes changes that can help in examining different query plans and selecting the best manually:

1. The log panel shows the position (in the list of query plans) of the executed query plan (always 1 unless a hint is provided), the total number of query plans in the list and the XML representation of the query plan (to help using the text hint).
2. Query hints can be embedded at the end of the query text in the format [[name=value]]. For example:

SELECT a.endDate,a.objectsInCharge.nodePath,a.objectsInCharge.properties.doubleValue
FROM Action a
WHERE a.type = 0 and a.subType = 3 and a.state = 3 and a.endDate >= {d '2018-07-20'} and
     (a.objectsInCharge.linkedObjects.classIdentifier = '(TC)' and
      a.objectsInCharge.linkedObjects.objectNameUpper = 'SETUP' and
      a.objectsInCharge.linkedObjects.type = 1) and
     (a.objectsInCharge.properties.name = 'scheduledSlot')
GROUP BY a.endDate, a.objectsInCharge.nodePath, a.objectsInCharge.properties.doubleValue
[[objectdb.query-plan-pos=2]]

or

SELECT a.endDate,a.objectsInCharge.nodePath,a.objectsInCharge.properties.doubleValue
FROM Action a
WHERE a.type = 0 and a.subType = 3 and a.state = 3 and a.endDate >= {d '2018-07-20'} and
     (a.objectsInCharge.linkedObjects.classIdentifier = '(TC)' and
      a.objectsInCharge.linkedObjects.objectNameUpper = 'SETUP' and
      a.objectsInCharge.linkedObjects.type = 1) and
     (a.objectsInCharge.properties.name = 'scheduledSlot')
GROUP BY a.endDate, a.objectsInCharge.nodePath, a.objectsInCharge.properties.doubleValue
[[objectdb.query-plan-text=ObjectNode[set]]]

You can also automate the selection and add to your application code that tests the top 5 query plans of each heavy query and select the fastest one. Similar mechanism will probably be added in future to ObjectDB.

we are a bit lost, where do we find that list/sequence of query plans ?

You can see all the query plans in the log if you enable logging for queries in trace level:

    <logger name="query" level="trace" />

It is shown as a <finalPlans> xml element with child for each query plan:

(however, it may be easier just to try running the query several times in the Explorer with the position hint: 1, 2, 3, 4, 5 as explained above).

<finalPlans>
    <groupPlan plan="group([a.endDate,v$1.nodePath,v$3.doubleValue],extract(v$1,a:extract(a,index(esst[3_3_0_2018-07-20->3_3_0:notNull])) <x> v$1:bound(a.objectsInCharge)) <x> v$2:filter(index(ctso['(TC)'_1->'(TC)'_1:and((v$2.objectNameUpper='SETUP'),notNull)]),(v$1.linkedObjects join v$2)) <x> v$3:filter(extract(v$3,type(ObjectProperty[set])),(v$3.name='scheduledSlot')))" eval="9.2895" group="group(a.endDate,v$1.nodePath,v$3.doubleValue)" eval="[]">
        <multiVarPlan plan="extract(v$1,a:extract(a,index(esst[3_3_0_2018-07-20->3_3_0:notNull])) <x> v$1:bound(a.objectsInCharge)) <x> v$2:filter(index(ctso['(TC)'_1->'(TC)'_1:and((v$2.objectNameUpper='SETUP'),notNull)]),(v$1.linkedObjects join v$2)) <x> v$3:filter(extract(v$3,type(ObjectProperty[set])),(v$3.name='scheduledSlot'))" eval="9.0342">
            <multiVarPlan plan="extract(v$1,a:extract(a,index(esst[3_3_0_2018-07-20->3_3_0:notNull])) <x> v$1:bound(a.objectsInCharge)) <x> v$2:filter(index(ctso['(TC)'_1->'(TC)'_1:and((v$2.objectNameUpper='SETUP'),notNull)]),(v$1.linkedObjects join v$2))" eval="6.9918">
                <extractPlan plan="extract(v$1,a:extract(a,index(esst[3_3_0_2018-07-20->3_3_0:notNull])) <x> v$1:bound(a.objectsInCharge))" eval="4.9675" variable="v$1">
                    <multiVarPlan plan="a:extract(a,index(esst[3_3_0_2018-07-20->3_3_0:notNull])) <x> v$1:bound(a.objectsInCharge)" eval="2.9675">
                        <extractPlan plan="extract(a,index(esst[3_3_0_2018-07-20->3_3_0:notNull]))" eval="2.9675" variable="a">
                            <btreePlan plan="index(esst[3_3_0_2018-07-20->3_3_0:notNull])" eval="0.9675" variable="a" />
                        </extractPlan>
                        <boundPlan plan="bound(a.objectsInCharge)" eval="0.0" variable="v$1" />
                    </multiVarPlan>
                </extractPlan>
                <filterPlan plan="filter(index(ctso['(TC)'_1->'(TC)'_1:and((v$2.objectNameUpper='SETUP'),notNull)]),(v$1.linkedObjects join v$2))" eval="1.0122">
                    <btreePlan plan="index(ctso['(TC)'_1->'(TC)'_1:and((v$2.objectNameUpper='SETUP'),notNull)])" eval="0.991" variable="v$2" />
                </filterPlan>
            </multiVarPlan>
            <filterPlan plan="filter(extract(v$3,type(ObjectProperty[set])),(v$3.name='scheduledSlot'))" eval="1.0212">
                <extractPlan plan="extract(v$3,type(ObjectProperty[set]))" eval="1.0" variable="v$3">
                    <btreePlan plan="type(ObjectProperty[set])" eval="1.0" variable="v$3" />
                </extractPlan>
            </filterPlan>
        </multiVarPlan>
    </groupPlan>
    <groupPlan plan="group([a.endDate,v$1.nodePath,v$3.doubleValue],extract(v$1,a:extract(a,index(esst[3_3_0_2018-07-20->3_3_0:notNull])) <x> v$1:bound(a.objectsInCharge)) <x> v$3:filter(extract(v$3,type(ObjectProperty[set])),(v$3.name='scheduledSlot')) <x> v$2:filter(filter(filter(extract(v$2,type(ObjectNode[set])),(v$2.type=1)),(v$2.objectNameUpper='SETUP')),(v$2.classIdentifier='(TC)')))" eval="9.3923" group="group(a.endDate,v$1.nodePath,v$3.doubleValue)" eval="[]">
        <multiVarPlan plan="extract(v$1,a:extract(a,index(esst[3_3_0_2018-07-20->3_3_0:notNull])) <x> v$1:bound(a.objectsInCharge)) <x> v$3:filter(extract(v$3,type(ObjectProperty[set])),(v$3.name='scheduledSlot')) <x> v$2:filter(filter(filter(extract(v$2,type(ObjectNode[set])),(v$2.type=1)),(v$2.objectNameUpper='SETUP')),(v$2.classIdentifier='(TC)'))" eval="9.137">
            <multiVarPlan plan="extract(v$1,a:extract(a,index(esst[3_3_0_2018-07-20->3_3_0:notNull])) <x> v$1:bound(a.objectsInCharge)) <x> v$3:filter(extract(v$3,type(ObjectProperty[set])),(v$3.name='scheduledSlot'))" eval="7.0099">
                <extractPlan plan="extract(v$1,a:extract(a,index(esst[3_3_0_2018-07-20->3_3_0:notNull])) <x> v$1:bound(a.objectsInCharge))" eval="4.9675" variable="v$1">
                    <multiVarPlan plan="a:extract(a,index(esst[3_3_0_2018-07-20->3_3_0:notNull])) <x> v$1:bound(a.objectsInCharge)" eval="2.9675">
                        <extractPlan plan="extract(a,index(esst[3_3_0_2018-07-20->3_3_0:notNull]))" eval="2.9675" variable="a">
                            <btreePlan plan="index(esst[3_3_0_2018-07-20->3_3_0:notNull])" eval="0.9675" variable="a" />
                        </extractPlan>
                        <boundPlan plan="bound(a.objectsInCharge)" eval="0.0" variable="v$1" />
                    </multiVarPlan>
                </extractPlan>
                <filterPlan plan="filter(extract(v$3,type(ObjectProperty[set])),(v$3.name='scheduledSlot'))" eval="1.0212">
                    <extractPlan plan="extract(v$3,type(ObjectProperty[set]))" eval="1.0" variable="v$3">
                        <btreePlan plan="type(ObjectProperty[set])" eval="1.0" variable="v$3" />
                    </extractPlan>
                </filterPlan>
            </multiVarPlan>
            <filterPlan plan="filter(filter(filter(extract(v$2,type(ObjectNode[set])),(v$2.type=1)),(v$2.objectNameUpper='SETUP')),(v$2.classIdentifier='(TC)'))" eval="1.0636">
                <filterPlan plan="filter(filter(extract(v$2,type(ObjectNode[set])),(v$2.type=1)),(v$2.objectNameUpper='SETUP'))" eval="1.0424">
                    <filterPlan plan="filter(extract(v$2,type(ObjectNode[set])),(v$2.type=1))" eval="1.0212">
                        <extractPlan plan="extract(v$2,type(ObjectNode[set]))" eval="1.0" variable="v$2">
                            <btreePlan plan="type(ObjectNode[set])" eval="1.0" variable="v$2" />
                        </extractPlan>
                    </filterPlan>
                </filterPlan>
            </filterPlan>
        </multiVarPlan>
    </groupPlan>
    <groupPlan plan="group([a.endDate,v$1.nodePath,v$3.doubleValue],extract(v$1,a:extract(a,index(esst[3_3_0_2018-07-20->3_3_0:notNull])) <x> v$1:type(ObjectNode[set])) <x> v$3:filter(extract(v$3,type(ObjectProperty[set])),(v$3.name='scheduledSlot')) <x> v$2:filter(index(ctso['(TC)'_1->'(TC)'_1:and((v$2.objectNameUpper='SETUP'),notNull)]),(v$1.linkedObjects join v$2)))" eval="9.5905" group="group(a.endDate,v$1.nodePath,v$3.doubleValue)" eval="[]">
        <multiVarPlan plan="extract(v$1,a:extract(a,index(esst[3_3_0_2018-07-20->3_3_0:notNull])) <x> v$1:type(ObjectNode[set])) <x> v$3:filter(extract(v$3,type(ObjectProperty[set])),(v$3.name='scheduledSlot')) <x> v$2:filter(index(ctso['(TC)'_1->'(TC)'_1:and((v$2.objectNameUpper='SETUP'),notNull)]),(v$1.linkedObjects join v$2))" eval="9.3352">
            <multiVarPlan plan="extract(v$1,a:extract(a,index(esst[3_3_0_2018-07-20->3_3_0:notNull])) <x> v$1:type(ObjectNode[set])) <x> v$3:filter(extract(v$3,type(ObjectProperty[set])),(v$3.name='scheduledSlot'))" eval="7.3109">
                <extractPlan plan="extract(v$1,a:extract(a,index(esst[3_3_0_2018-07-20->3_3_0:notNull])) <x> v$1:type(ObjectNode[set]))" eval="5.2685" variable="v$1">
                    <multiVarPlan plan="a:extract(a,index(esst[3_3_0_2018-07-20->3_3_0:notNull])) <x> v$1:type(ObjectNode[set])" eval="5.2685">
                        <extractPlan plan="extract(a,index(esst[3_3_0_2018-07-20->3_3_0:notNull]))" eval="2.9675" variable="a">
                            <btreePlan plan="index(esst[3_3_0_2018-07-20->3_3_0:notNull])" eval="0.9675" variable="a" />
                        </extractPlan>
                        <btreePlan plan="type(ObjectNode[set])" eval="1.0" variable="v$1" />
                    </multiVarPlan>
                </extractPlan>
                <filterPlan plan="filter(extract(v$3,type(ObjectProperty[set])),(v$3.name='scheduledSlot'))" eval="1.0212">
                    <extractPlan plan="extract(v$3,type(ObjectProperty[set]))" eval="1.0" variable="v$3">
                        <btreePlan plan="type(ObjectProperty[set])" eval="1.0" variable="v$3" />
                    </extractPlan>
                </filterPlan>
            </multiVarPlan>
            <filterPlan plan="filter(index(ctso['(TC)'_1->'(TC)'_1:and((v$2.objectNameUpper='SETUP'),notNull)]),(v$1.linkedObjects join v$2))" eval="1.0122">
                <btreePlan plan="index(ctso['(TC)'_1->'(TC)'_1:and((v$2.objectNameUpper='SETUP'),notNull)])" eval="0.991" variable="v$2" />
            </filterPlan>
        </multiVarPlan>
    </groupPlan>
    <groupPlan plan="group([a.endDate,v$1.nodePath,v$3.doubleValue],extract(v$1,a:extract(a,index(esst[3_3_0_2018-07-20->3_3_0:notNull])) <x> v$1:type(ObjectNode[set])) <x> v$3:filter(extract(v$3,type(ObjectProperty[set])),(v$3.name='scheduledSlot')) <x> v$2:filter(filter(filter(extract(v$2,type(ObjectNode[set])),(v$2.type=1)),(v$2.objectNameUpper='SETUP')),(v$2.classIdentifier='(TC)')))" eval="9.6933" group="group(a.endDate,v$1.nodePath,v$3.doubleValue)" eval="[]">
        <multiVarPlan plan="extract(v$1,a:extract(a,index(esst[3_3_0_2018-07-20->3_3_0:notNull])) <x> v$1:type(ObjectNode[set])) <x> v$3:filter(extract(v$3,type(ObjectProperty[set])),(v$3.name='scheduledSlot')) <x> v$2:filter(filter(filter(extract(v$2,type(ObjectNode[set])),(v$2.type=1)),(v$2.objectNameUpper='SETUP')),(v$2.classIdentifier='(TC)'))" eval="9.438">
            <multiVarPlan plan="extract(v$1,a:extract(a,index(esst[3_3_0_2018-07-20->3_3_0:notNull])) <x> v$1:type(ObjectNode[set])) <x> v$3:filter(extract(v$3,type(ObjectProperty[set])),(v$3.name='scheduledSlot'))" eval="7.3109">
                <extractPlan plan="extract(v$1,a:extract(a,index(esst[3_3_0_2018-07-20->3_3_0:notNull])) <x> v$1:type(ObjectNode[set]))" eval="5.2685" variable="v$1">
                    <multiVarPlan plan="a:extract(a,index(esst[3_3_0_2018-07-20->3_3_0:notNull])) <x> v$1:type(ObjectNode[set])" eval="5.2685">
                        <extractPlan plan="extract(a,index(esst[3_3_0_2018-07-20->3_3_0:notNull]))" eval="2.9675" variable="a">
                            <btreePlan plan="index(esst[3_3_0_2018-07-20->3_3_0:notNull])" eval="0.9675" variable="a" />
                        </extractPlan>
                        <btreePlan plan="type(ObjectNode[set])" eval="1.0" variable="v$1" />
                    </multiVarPlan>
                </extractPlan>
                <filterPlan plan="filter(extract(v$3,type(ObjectProperty[set])),(v$3.name='scheduledSlot'))" eval="1.0212">
                    <extractPlan plan="extract(v$3,type(ObjectProperty[set]))" eval="1.0" variable="v$3">
                        <btreePlan plan="type(ObjectProperty[set])" eval="1.0" variable="v$3" />
                    </extractPlan>
                </filterPlan>
            </multiVarPlan>
            <filterPlan plan="filter(filter(filter(extract(v$2,type(ObjectNode[set])),(v$2.type=1)),(v$2.objectNameUpper='SETUP')),(v$2.classIdentifier='(TC)'))" eval="1.0636">
                <filterPlan plan="filter(filter(extract(v$2,type(ObjectNode[set])),(v$2.type=1)),(v$2.objectNameUpper='SETUP'))" eval="1.0424">
                    <filterPlan plan="filter(extract(v$2,type(ObjectNode[set])),(v$2.type=1))" eval="1.0212">
                        <extractPlan plan="extract(v$2,type(ObjectNode[set]))" eval="1.0" variable="v$2">
                            <btreePlan plan="type(ObjectNode[set])" eval="1.0" variable="v$2" />
                        </extractPlan>
                    </filterPlan>
                </filterPlan>
            </filterPlan>
        </multiVarPlan>
    </groupPlan>
    <groupPlan plan="group([a.endDate,v$1.nodePath,v$3.doubleValue],extract(v$1,a:filter(filter(filter(filter(extract(a,type(Action[all])),(a.endDate>=current-java.sql.Date)),(a.state=3)),(a.subType=3)),(a.type=0)) <x> v$1:bound(a.objectsInCharge)) <x> v$3:filter(extract(v$3,type(ObjectProperty[set])),(v$3.name='scheduledSlot')) <x> v$2:filter(index(ctso['(TC)'_1->'(TC)'_1:and((v$2.objectNameUpper='SETUP'),notNull)]),(v$1.linkedObjects join v$2)))" eval="18.6359" group="group(a.endDate,v$1.nodePath,v$3.doubleValue)" eval="[]">
        <multiVarPlan plan="extract(v$1,a:filter(filter(filter(filter(extract(a,type(Action[all])),(a.endDate>=current-java.sql.Date)),(a.state=3)),(a.subType=3)),(a.type=0)) <x> v$1:bound(a.objectsInCharge)) <x> v$3:filter(extract(v$3,type(ObjectProperty[set])),(v$3.name='scheduledSlot')) <x> v$2:filter(index(ctso['(TC)'_1->'(TC)'_1:and((v$2.objectNameUpper='SETUP'),notNull)]),(v$1.linkedObjects join v$2))" eval="18.3806">
            <multiVarPlan plan="extract(v$1,a:filter(filter(filter(filter(extract(a,type(Action[all])),(a.endDate>=current-java.sql.Date)),(a.state=3)),(a.subType=3)),(a.type=0)) <x> v$1:bound(a.objectsInCharge)) <x> v$3:filter(extract(v$3,type(ObjectProperty[set])),(v$3.name='scheduledSlot'))" eval="16.3563">
                <extractPlan plan="extract(v$1,a:filter(filter(filter(filter(extract(a,type(Action[all])),(a.endDate>=current-java.sql.Date)),(a.state=3)),(a.subType=3)),(a.type=0)) <x> v$1:bound(a.objectsInCharge))" eval="14.3139" variable="v$1">
                    <multiVarPlan plan="a:filter(filter(filter(filter(extract(a,type(Action[all])),(a.endDate>=current-java.sql.Date)),(a.state=3)),(a.subType=3)),(a.type=0)) <x> v$1:bound(a.objectsInCharge)" eval="12.3139">
                        <filterPlan plan="filter(filter(filter(filter(extract(a,type(Action[all])),(a.endDate>=current-java.sql.Date)),(a.state=3)),(a.subType=3)),(a.type=0))" eval="12.3139">
                            <filterPlan plan="filter(filter(filter(extract(a,type(Action[all])),(a.endDate>=current-java.sql.Date)),(a.state=3)),(a.subType=3))" eval="12.2927">
                                <filterPlan plan="filter(filter(extract(a,type(Action[all])),(a.endDate>=current-java.sql.Date)),(a.state=3))" eval="12.2715">
                                    <filterPlan plan="filter(extract(a,type(Action[all])),(a.endDate>=current-java.sql.Date))" eval="12.2503">
                                        <extractPlan plan="extract(a,type(Action[all]))" eval="12.2291" variable="a">
                                            <btreePlan plan="type(Action[all])" eval="11.9281" variable="a" />
                                        </extractPlan>
                                    </filterPlan>
                                </filterPlan>
                            </filterPlan>
                        </filterPlan>
                        <boundPlan plan="bound(a.objectsInCharge)" eval="0.0" variable="v$1" />
                    </multiVarPlan>
                </extractPlan>
                <filterPlan plan="filter(extract(v$3,type(ObjectProperty[set])),(v$3.name='scheduledSlot'))" eval="1.0212">
                    <extractPlan plan="extract(v$3,type(ObjectProperty[set]))" eval="1.0" variable="v$3">
                        <btreePlan plan="type(ObjectProperty[set])" eval="1.0" variable="v$3" />
                    </extractPlan>
                </filterPlan>
            </multiVarPlan>
            <filterPlan plan="filter(index(ctso['(TC)'_1->'(TC)'_1:and((v$2.objectNameUpper='SETUP'),notNull)]),(v$1.linkedObjects join v$2))" eval="1.0122">
                <btreePlan plan="index(ctso['(TC)'_1->'(TC)'_1:and((v$2.objectNameUpper='SETUP'),notNull)])" eval="0.991" variable="v$2" />
            </filterPlan>
        </multiVarPlan>
    </groupPlan>
    <groupPlan plan="group([a.endDate,v$1.nodePath,v$3.doubleValue],extract(v$1,a:filter(filter(filter(filter(extract(a,type(Action[all])),(a.endDate>=current-java.sql.Date)),(a.state=3)),(a.subType=3)),(a.type=0)) <x> v$1:bound(a.objectsInCharge)) <x> v$2:filter(filter(filter(extract(v$2,type(ObjectNode[set])),(v$2.type=1)),(v$2.objectNameUpper='SETUP')),(v$2.classIdentifier='(TC)')) <x> v$3:filter(extract(v$3,type(ObjectProperty[set])),(v$3.name='scheduledSlot')))" eval="18.7387" group="group(a.endDate,v$1.nodePath,v$3.doubleValue)" eval="[]">
        <multiVarPlan plan="extract(v$1,a:filter(filter(filter(filter(extract(a,type(Action[all])),(a.endDate>=current-java.sql.Date)),(a.state=3)),(a.subType=3)),(a.type=0)) <x> v$1:bound(a.objectsInCharge)) <x> v$2:filter(filter(filter(extract(v$2,type(ObjectNode[set])),(v$2.type=1)),(v$2.objectNameUpper='SETUP')),(v$2.classIdentifier='(TC)')) <x> v$3:filter(extract(v$3,type(ObjectProperty[set])),(v$3.name='scheduledSlot'))" eval="18.4834">
            <multiVarPlan plan="extract(v$1,a:filter(filter(filter(filter(extract(a,type(Action[all])),(a.endDate>=current-java.sql.Date)),(a.state=3)),(a.subType=3)),(a.type=0)) <x> v$1:bound(a.objectsInCharge)) <x> v$2:filter(filter(filter(extract(v$2,type(ObjectNode[set])),(v$2.type=1)),(v$2.objectNameUpper='SETUP')),(v$2.classIdentifier='(TC)'))" eval="16.441">
                <extractPlan plan="extract(v$1,a:filter(filter(filter(filter(extract(a,type(Action[all])),(a.endDate>=current-java.sql.Date)),(a.state=3)),(a.subType=3)),(a.type=0)) <x> v$1:bound(a.objectsInCharge))" eval="14.3139" variable="v$1">
                    <multiVarPlan plan="a:filter(filter(filter(filter(extract(a,type(Action[all])),(a.endDate>=current-java.sql.Date)),(a.state=3)),(a.subType=3)),(a.type=0)) <x> v$1:bound(a.objectsInCharge)" eval="12.3139">
                        <filterPlan plan="filter(filter(filter(filter(extract(a,type(Action[all])),(a.endDate>=current-java.sql.Date)),(a.state=3)),(a.subType=3)),(a.type=0))" eval="12.3139">
                            <filterPlan plan="filter(filter(filter(extract(a,type(Action[all])),(a.endDate>=current-java.sql.Date)),(a.state=3)),(a.subType=3))" eval="12.2927">
                                <filterPlan plan="filter(filter(extract(a,type(Action[all])),(a.endDate>=current-java.sql.Date)),(a.state=3))" eval="12.2715">
                                    <filterPlan plan="filter(extract(a,type(Action[all])),(a.endDate>=current-java.sql.Date))" eval="12.2503">
                                        <extractPlan plan="extract(a,type(Action[all]))" eval="12.2291" variable="a">
                                            <btreePlan plan="type(Action[all])" eval="11.9281" variable="a" />
                                        </extractPlan>
                                    </filterPlan>
                                </filterPlan>
                            </filterPlan>
                        </filterPlan>
                        <boundPlan plan="bound(a.objectsInCharge)" eval="0.0" variable="v$1" />
                    </multiVarPlan>
                </extractPlan>
                <filterPlan plan="filter(filter(filter(extract(v$2,type(ObjectNode[set])),(v$2.type=1)),(v$2.objectNameUpper='SETUP')),(v$2.classIdentifier='(TC)'))" eval="1.0636">
                    <filterPlan plan="filter(filter(extract(v$2,type(ObjectNode[set])),(v$2.type=1)),(v$2.objectNameUpper='SETUP'))" eval="1.0424">
                        <filterPlan plan="filter(extract(v$2,type(ObjectNode[set])),(v$2.type=1))" eval="1.0212">
                            <extractPlan plan="extract(v$2,type(ObjectNode[set]))" eval="1.0" variable="v$2">
                                <btreePlan plan="type(ObjectNode[set])" eval="1.0" variable="v$2" />
                            </extractPlan>
                        </filterPlan>
                    </filterPlan>
                </filterPlan>
            </multiVarPlan>
            <filterPlan plan="filter(extract(v$3,type(ObjectProperty[set])),(v$3.name='scheduledSlot'))" eval="1.0212">
                <extractPlan plan="extract(v$3,type(ObjectProperty[set]))" eval="1.0" variable="v$3">
                    <btreePlan plan="type(ObjectProperty[set])" eval="1.0" variable="v$3" />
                </extractPlan>
            </filterPlan>
        </multiVarPlan>
    </groupPlan>
    <groupPlan plan="group([a.endDate,v$1.nodePath,v$3.doubleValue],extract(v$1,a:filter(filter(filter(filter(extract(a,type(Action[all])),(a.endDate>=current-java.sql.Date)),(a.state=3)),(a.subType=3)),(a.type=0)) <x> v$1:type(ObjectNode[set])) <x> v$3:filter(extract(v$3,type(ObjectProperty[set])),(v$3.name='scheduledSlot')) <x> v$2:filter(index(ctso['(TC)'_1->'(TC)'_1:and((v$2.objectNameUpper='SETUP'),notNull)]),(v$1.linkedObjects join v$2)))" eval="18.9369" group="group(a.endDate,v$1.nodePath,v$3.doubleValue)" eval="[]">
        <multiVarPlan plan="extract(v$1,a:filter(filter(filter(filter(extract(a,type(Action[all])),(a.endDate>=current-java.sql.Date)),(a.state=3)),(a.subType=3)),(a.type=0)) <x> v$1:type(ObjectNode[set])) <x> v$3:filter(extract(v$3,type(ObjectProperty[set])),(v$3.name='scheduledSlot')) <x> v$2:filter(index(ctso['(TC)'_1->'(TC)'_1:and((v$2.objectNameUpper='SETUP'),notNull)]),(v$1.linkedObjects join v$2))" eval="18.6816">
            <multiVarPlan plan="extract(v$1,a:filter(filter(filter(filter(extract(a,type(Action[all])),(a.endDate>=current-java.sql.Date)),(a.state=3)),(a.subType=3)),(a.type=0)) <x> v$1:type(ObjectNode[set])) <x> v$3:filter(extract(v$3,type(ObjectProperty[set])),(v$3.name='scheduledSlot'))" eval="16.6573">
                <extractPlan plan="extract(v$1,a:filter(filter(filter(filter(extract(a,type(Action[all])),(a.endDate>=current-java.sql.Date)),(a.state=3)),(a.subType=3)),(a.type=0)) <x> v$1:type(ObjectNode[set]))" eval="14.6149" variable="v$1">
                    <multiVarPlan plan="a:filter(filter(filter(filter(extract(a,type(Action[all])),(a.endDate>=current-java.sql.Date)),(a.state=3)),(a.subType=3)),(a.type=0)) <x> v$1:type(ObjectNode[set])" eval="14.6149">
                        <filterPlan plan="filter(filter(filter(filter(extract(a,type(Action[all])),(a.endDate>=current-java.sql.Date)),(a.state=3)),(a.subType=3)),(a.type=0))" eval="12.3139">
                            <filterPlan plan="filter(filter(filter(extract(a,type(Action[all])),(a.endDate>=current-java.sql.Date)),(a.state=3)),(a.subType=3))" eval="12.2927">
                                <filterPlan plan="filter(filter(extract(a,type(Action[all])),(a.endDate>=current-java.sql.Date)),(a.state=3))" eval="12.2715">
                                    <filterPlan plan="filter(extract(a,type(Action[all])),(a.endDate>=current-java.sql.Date))" eval="12.2503">
                                        <extractPlan plan="extract(a,type(Action[all]))" eval="12.2291" variable="a">
                                            <btreePlan plan="type(Action[all])" eval="11.9281" variable="a" />
                                        </extractPlan>
                                    </filterPlan>
                                </filterPlan>
                            </filterPlan>
                        </filterPlan>
                        <btreePlan plan="type(ObjectNode[set])" eval="1.0" variable="v$1" />
                    </multiVarPlan>
                </extractPlan>
                <filterPlan plan="filter(extract(v$3,type(ObjectProperty[set])),(v$3.name='scheduledSlot'))" eval="1.0212">
                    <extractPlan plan="extract(v$3,type(ObjectProperty[set]))" eval="1.0" variable="v$3">
                        <btreePlan plan="type(ObjectProperty[set])" eval="1.0" variable="v$3" />
                    </extractPlan>
                </filterPlan>
            </multiVarPlan>
            <filterPlan plan="filter(index(ctso['(TC)'_1->'(TC)'_1:and((v$2.objectNameUpper='SETUP'),notNull)]),(v$1.linkedObjects join v$2))" eval="1.0122">
                <btreePlan plan="index(ctso['(TC)'_1->'(TC)'_1:and((v$2.objectNameUpper='SETUP'),notNull)])" eval="0.991" variable="v$2" />
            </filterPlan>
        </multiVarPlan>
    </groupPlan>
    <groupPlan plan="group([a.endDate,v$1.nodePath,v$3.doubleValue],extract(v$1,a:filter(filter(filter(filter(extract(a,type(Action[all])),(a.endDate>=current-java.sql.Date)),(a.state=3)),(a.subType=3)),(a.type=0)) <x> v$1:type(ObjectNode[set])) <x> v$3:filter(extract(v$3,type(ObjectProperty[set])),(v$3.name='scheduledSlot')) <x> v$2:filter(filter(filter(extract(v$2,type(ObjectNode[set])),(v$2.type=1)),(v$2.objectNameUpper='SETUP')),(v$2.classIdentifier='(TC)')))" eval="19.0397" group="group(a.endDate,v$1.nodePath,v$3.doubleValue)" eval="[]">
        <multiVarPlan plan="extract(v$1,a:filter(filter(filter(filter(extract(a,type(Action[all])),(a.endDate>=current-java.sql.Date)),(a.state=3)),(a.subType=3)),(a.type=0)) <x> v$1:type(ObjectNode[set])) <x> v$3:filter(extract(v$3,type(ObjectProperty[set])),(v$3.name='scheduledSlot')) <x> v$2:filter(filter(filter(extract(v$2,type(ObjectNode[set])),(v$2.type=1)),(v$2.objectNameUpper='SETUP')),(v$2.classIdentifier='(TC)'))" eval="18.7844">
            <multiVarPlan plan="extract(v$1,a:filter(filter(filter(filter(extract(a,type(Action[all])),(a.endDate>=current-java.sql.Date)),(a.state=3)),(a.subType=3)),(a.type=0)) <x> v$1:type(ObjectNode[set])) <x> v$3:filter(extract(v$3,type(ObjectProperty[set])),(v$3.name='scheduledSlot'))" eval="16.6573">
                <extractPlan plan="extract(v$1,a:filter(filter(filter(filter(extract(a,type(Action[all])),(a.endDate>=current-java.sql.Date)),(a.state=3)),(a.subType=3)),(a.type=0)) <x> v$1:type(ObjectNode[set]))" eval="14.6149" variable="v$1">
                    <multiVarPlan plan="a:filter(filter(filter(filter(extract(a,type(Action[all])),(a.endDate>=current-java.sql.Date)),(a.state=3)),(a.subType=3)),(a.type=0)) <x> v$1:type(ObjectNode[set])" eval="14.6149">
                        <filterPlan plan="filter(filter(filter(filter(extract(a,type(Action[all])),(a.endDate>=current-java.sql.Date)),(a.state=3)),(a.subType=3)),(a.type=0))" eval="12.3139">
                            <filterPlan plan="filter(filter(filter(extract(a,type(Action[all])),(a.endDate>=current-java.sql.Date)),(a.state=3)),(a.subType=3))" eval="12.2927">
                                <filterPlan plan="filter(filter(extract(a,type(Action[all])),(a.endDate>=current-java.sql.Date)),(a.state=3))" eval="12.2715">
                                    <filterPlan plan="filter(extract(a,type(Action[all])),(a.endDate>=current-java.sql.Date))" eval="12.2503">
                                        <extractPlan plan="extract(a,type(Action[all]))" eval="12.2291" variable="a">
                                            <btreePlan plan="type(Action[all])" eval="11.9281" variable="a" />
                                        </extractPlan>
                                    </filterPlan>
                                </filterPlan>
                            </filterPlan>
                        </filterPlan>
                        <btreePlan plan="type(ObjectNode[set])" eval="1.0" variable="v$1" />
                    </multiVarPlan>
                </extractPlan>
                <filterPlan plan="filter(extract(v$3,type(ObjectProperty[set])),(v$3.name='scheduledSlot'))" eval="1.0212">
                    <extractPlan plan="extract(v$3,type(ObjectProperty[set]))" eval="1.0" variable="v$3">
                        <btreePlan plan="type(ObjectProperty[set])" eval="1.0" variable="v$3" />
                    </extractPlan>
                </filterPlan>
            </multiVarPlan>
            <filterPlan plan="filter(filter(filter(extract(v$2,type(ObjectNode[set])),(v$2.type=1)),(v$2.objectNameUpper='SETUP')),(v$2.classIdentifier='(TC)'))" eval="1.0636">
                <filterPlan plan="filter(filter(extract(v$2,type(ObjectNode[set])),(v$2.type=1)),(v$2.objectNameUpper='SETUP'))" eval="1.0424">
                    <filterPlan plan="filter(extract(v$2,type(ObjectNode[set])),(v$2.type=1))" eval="1.0212">
                        <extractPlan plan="extract(v$2,type(ObjectNode[set]))" eval="1.0" variable="v$2">
                            <btreePlan plan="type(ObjectNode[set])" eval="1.0" variable="v$2" />
                        </extractPlan>
                    </filterPlan>
                </filterPlan>
            </filterPlan>
        </multiVarPlan>
    </groupPlan>
</finalPlans>

Build 2.7.6_08 improves query plan evaluation and provides better selection of query plans by using additional statistic information. To benefit from these changes ObjectDB must collect that statistics. Currently this is done by running a special query:

    public static void main(String[] args) throws Exception
    {
        EntityManagerFactory emf =
            Persistence.createEntityManagerFactory("my.odb");
        EntityManager em = emf.createEntityManager();
        
        Thread thread = em.createQuery(
            "objectdb statistics", Thread.class).getSingleResult();
        thread.join();
        
        em.close();
        emf.close();
    }

It may take some time for this process to complete (about 20-30 minutes on your sample database). It may be effective to run this again occasionally when the database structure or content is changed significantly.

With build 2.7.6_08 and after collecting statistics as explained above the query execution is fast with no need for query hints. This also helps in similar issues (e.g. issue #2417).

Thanks, question:

do we have to run this query every time we start our application or are the statistics stored in the database file ?

Yes. The statistic info is stored in the database file and only replaced with new info when that query is run again (recommended when the database structure or content is changed significantly).

• we implemented version 2.8.0_02, added:
   

                    entityManager = entityManagerFactory.createEntityManager();

                    Thread thread = entityManager.createQuery("objectdb statistics", Thread.class).getSingleResult();

                    thread.join();

• on startup there is a significant waiting time in the above functionality
• queries are significantly slow, example query: (version 2.7.6 it takes 47 ms, version 2.8.0_02 it takes 700 ms):
  SELECT DISTINCT o.parentNode FROM ObjectNode o WHERE o.classIdentifier = "(OP)" AND (o.linkedObjects.classIdentifier = "(US)" and o.linkedObjects.objectNameUpper = "ORG" and o.linkedObjects.type = 1 and o.linkedObjects.state = 0) AND (o.parentNode.properties.name = "confirmed" AND o.parentNode.properties.doubleValue = 0) and o.parentNode.classIdentifier = "(ME)" order by o.parentNode.modified desc
• database can be downloaded from 

https://www.dropbox.com/s/px01hyoelov7gdx/coreSystemDb.odb.zip?dl=0

just to make clear: all queries are slow, not only the above example

Just tried that query on that database in the Explorer (of the most recent release) and it seems that a composite index was selected and used and that the execution time is less than 3 seconds. Could you please check running that query in the Explorer?

Execution time of this query fells to 1457 on repeating runs (when cache is disabled), will be checked now against version 2.7.6.

OK. With 2.7.6 it is indeed much faster. We will investigate the cause.

Some of the statistics in the database that you sent is broken, but it is unclear yet if this is the only issue.

Is it your first attempt to use a build starting 2.7.6_08 that just failed, or did you previously see some success with query execution with the new changes, before having this problem?

this was our first attempt. We also tried it with the bug database (that you have, too), same result

Build 2.8.0_03 was released now and hopefully it will work better.

There was a critical bug in running the "objectdb statistics" query. Please discard or repair using the Doctor any databases on which this query was run to avoid further issues. Then run the "objectdb statistics" query again using build 2.8.0_03. If you can post the database (from post #13 above) after this action we can verify that it contains statistics as expected.

Please report whether or not the new build runs queries faster now.

1. done Doctor repair
2. now we have an 'Unexpected internal exception' cause 'java.lang.ArrayIndexOutOfBoundsException: 2' exception on executing this query what we never had before:
   SELECT DISTINCT o from ObjectNode o join o.properties p1 join o.properties p2 where o.classIdentifier = '(OP)' and ((o.properties.name = 'scheduledSlot' and o.properties.doubleValue <= 2553) and (p2.name = 'scheduledEndSlot' and p2.doubleValue >= 2553 and p2.state = 0) and (p1.name = 'noCapacityLoad' and p1.doubleValue = 0))

using the small DB, here the current state of the db:

https://www.dropbox.com/s/1zm96q5trnfogy1/coreSystemDb.odb%202.zip?dl=0 

• The ArrayIndexOutOfBoundsException was caused by a recent change in 2.8.0_03. Please try 2.8.0_04 that should fix it.
• The database in #22 has partial statistics. You have to run the statistics query again on the repaired database. If it is not a production database and you can go back to the last backup before running the statistics query previously (without loosing data) - it would be better.

we did run the Doctor + the statistics query afterwards before running the problematic query #21

Strangely repeating this procedure of running the Doctor and then the Stat query on the database in #22 above resulted with a new database with full statistics (where the database of #22 itself has partial statistics).

Would you like to try repeating Doctor + Stat on it and send the result?

Or better, start with a new database with no previous stat running and run Stat query once?

we did now Doctore + stats query using the _04 and it looks better, we'll continue checking it, the current database is available here:
https://www.dropbox.com/s/i4wovoa0oq3wlgx/coreSystemDb.odb%203.zip?dl=0

Query plan selection in the new version may be better even without statistics, as there are several other improvements.  However, unfortunately your new database doesn't have the statistics yet, although there are indications that it was run several times. Without the statistics query plan selection and execution will not be optimal.

Here a small program that adds statistics to your database successfully:

import javax.persistence.*;

public class F2379
{
    public static void main(String[] args) throws Exception {
        
        System.setProperty("objectdb.temp.no-schema-upgrade", "true");
        
        EntityManagerFactory emf =
            Persistence.createEntityManagerFactory("D:\\odb-files\\coreSystemDb3.odb");
        EntityManager em = emf.createEntityManager();

        Thread thread = em.createQuery("objectdb statistics", Thread.class).getSingleResult();
        thread.join();
        
        em.close();
        emf.close();
    }        
}

It is unclear why it didn't work well when you ran it. Maybe there is another issue that has to be addressed. If you uploaded the odb file but there was also a recovery odb$ flle then this may explain why we cannot see the statistics, but maybe you have them, but it is unclear yet.

we are again in the process of doing the migration to 2.8.3, the questions are:

- can we now use a query hint without creating the statistics

- is there a possibility to give a hint not based on a string in the query plan but on the index itself ?

example query (question: how can we hint to go the direction it goes in 2.7.6 ?):

SELECT DISTINCT o from ObjectNode o join o.properties p1 join o.properties p2 where o.classIdentifier = '(OP)' and ((o.properties.name = 'scheduledSlot' and o.properties.doubleValue <= 2850 and o.properties.state = 0) and (p2.name = 'scheduledEndSlot' and p2.doubleValue >= 2850 and p2.state = 0) and (p1.name = 'noCapacityLoad' and p1.doubleValue = 0))

version 2.8.3 query plan (query takes minutes):

Query plan 1/16 description
============================

Step 1: Process ObjectNode (o) instances
----------------------------------------
[Step 1a]
Scan index com.agile.hummingbird.ObjectNode[classIdentifier]
locating ObjectNode (o) instances that satisfy: 
  (o.classIdentifier='(OP)').

[Step 1b]
Retrieve fields in ObjectNode (o) instances.

Step 2: Process ObjectProperty (v$1) instances
  (for every result of step 1)
----------------------------------------------
[Step 2a]
Iterate over all the instances (v$1) in o.properties.

[Step 2b]
Retrieve fields in ObjectProperty (v$1) instances.

[Step 2c]
Filter the results of step 2b
retaining only results that satisfy:
  (v$1.state=0).

[Step 2d]
Filter the results of step 2c
retaining only results that satisfy:
  (v$1.doubleValue<=2850).

...

in version 2.7.6 (query takes 60 ms):

Query Plan Description
======================

Step 1: Process ObjectProperty (p2) instances
---------------------------------------------
Scan index com.agile.hummingbird.ObjectProperty[name, state, doubleValue]
locating ObjectProperty (p2) instances that satisfy: 
  and(and((p2.name='scheduledEndSlot'),(p2.state=0)),(p2.doubleValue>=2850)).

Step 2: Process ObjectNode (o) instances
  (for every result of step 1)
----------------------------------------
[Step 2a]
Scan index com.agile.hummingbird.ObjectNode[properties]
locating ObjectNode (o) instances that satisfy: 
  (o.properties join p2).

[Step 2b]
Retrieve fields in ObjectNode (o) instances.

[Step 2c]
Filter the results of step 2b
retaining only results that satisfy:
  (o.classIdentifier='(OP)').

Step 3: Process ObjectProperty (p1) instances
  (for every result tuple of the steps above)
---------------------------------------------
[Step 3a]
Scan type com.agile.hummingbird.ObjectProperty
locating ObjectProperty (p1) instances that satisfy: 
  (o.properties join p1).
...

- can we now use a query hint without creating the statistics

Probably you can, maybe except in rare cases in which a query program is not produced at all (or filtered early in an internal step) due to missing statistics.

- is there a possibility to give a hint not based on a string in the query plan but on the index itself ?

It is should be possible. See #7 above.

- example query (question: how can we hint to go the direction it goes in 2.7.6 ?):

The query plan that you included in the last post is partial. If you can include the full log output of one query, as in #9 above, in both 2.7.6 and 2.8.3 then it may be possible to help you with this particular query (by selecting a proper string from the correct query plan).

2.7

Query Plan Description
======================

Step 1: Process ObjectProperty (p2) instances
---------------------------------------------
Scan index com.agile.hummingbird.ObjectProperty[name, state, doubleValue]
locating ObjectProperty (p2) instances that satisfy: 
  and(and((p2.name='scheduledEndSlot'),(p2.state=0)),(p2.doubleValue>=2850)).

Step 2: Process ObjectNode (o) instances
  (for every result of step 1)
----------------------------------------
[Step 2a]
Scan index com.agile.hummingbird.ObjectNode[properties]
locating ObjectNode (o) instances that satisfy: 
  (o.properties join p2).

[Step 2b]
Retrieve fields in ObjectNode (o) instances.

[Step 2c]
Filter the results of step 2b
retaining only results that satisfy:
  (o.classIdentifier='(OP)').

Step 3: Process ObjectProperty (p1) instances
  (for every result tuple of the steps above)
---------------------------------------------
[Step 3a]
Scan type com.agile.hummingbird.ObjectProperty
locating ObjectProperty (p1) instances that satisfy: 
  (o.properties join p1).

[Step 3b]
Evaluate fields in ObjectProperty (p1) instances.

[Step 3c]
Filter the results of step 3b
retaining only results that satisfy:
  (p1.doubleValue=0).

[Step 3d]
Filter the results of step 3c
retaining only results that satisfy:
  (p1.name='noCapacityLoad').

Step 4: Process ObjectProperty (v$1) instances
  (for every result tuple of the steps above)
----------------------------------------------
[Step 4a]
Scan index com.agile.hummingbird.ObjectProperty[name, state, doubleValue]
locating ObjectProperty (v$1) instances that satisfy: 
  and(and((v$1.name='scheduledSlot'),(v$1.state=0)),(v$1.doubleValue<=2850)).

[Step 4b]
Filter the results of step 4a
retaining only results that satisfy:
  (o.properties join v$1).

Step 5: Group results
---------------------
Group the results of step 4 by:
  o.

[Step 6]
Remove duplicates from the groups of step 5.

[Step 7]
Apply selection and prepare final results.
 

2.8

Query plan 1/16 description
============================

Step 1: Process ObjectNode (o) instances
----------------------------------------
[Step 1a]
Scan index com.agile.hummingbird.ObjectNode[classIdentifier]
locating ObjectNode (o) instances that satisfy: 
  (o.classIdentifier='(OP)').

[Step 1b]
Retrieve fields in ObjectNode (o) instances.

Step 2: Process ObjectProperty (v$1) instances
  (for every result of step 1)
----------------------------------------------
[Step 2a]
Iterate over all the instances (v$1) in o.properties.

[Step 2b]
Retrieve fields in ObjectProperty (v$1) instances.

[Step 2c]
Filter the results of step 2b
retaining only results that satisfy:
  (v$1.state=0).

[Step 2d]
Filter the results of step 2c
retaining only results that satisfy:
  (v$1.doubleValue<=2850).

[Step 2e]
Filter the results of step 2d
retaining only results that satisfy:
  (v$1.name='scheduledSlot').

Step 3: Process ObjectProperty (p2) instances
  (for every result tuple of the steps above)
---------------------------------------------
[Step 3a]
Iterate over all the instances (p2) in o.properties.

[Step 3b]
Retrieve fields in ObjectProperty (p2) instances.

[Step 3c]
Filter the results of step 3b
retaining only results that satisfy:
  (p2.state=0).

[Step 3d]
Filter the results of step 3c
retaining only results that satisfy:
  (p2.doubleValue>=2850).

[Step 3e]
Filter the results of step 3d
retaining only results that satisfy:
  (p2.name='scheduledEndSlot').

Step 4: Process ObjectProperty (p1) instances
  (for every result tuple of the steps above)
---------------------------------------------
[Step 4a]
Iterate over all the instances (p1) in o.properties.

[Step 4b]
Retrieve fields in ObjectProperty (p1) instances.

[Step 4c]
Filter the results of step 4b
retaining only results that satisfy:
  (p1.doubleValue=0).

[Step 4d]
Filter the results of step 4c
retaining only results that satisfy:
  (p1.name='noCapacityLoad').

Step 5: Apply selection
-----------------------
Apply selection and prepare final results.

<multiVarPlan 15.3477 3,34/2,26 o,p1,p2,v$1>
  <multiVarPlan 11.2629 3,21/2,26 o,p2,v$1>
    <multiVarPlan 7.1358 3,00/2,26 o,v$1>
      <extractPlan 3.0087 2,56/2,26 o(ObjectNode)>
        <btreePlan 1.0087 0,70/2,26 o(ObjectNode) index(-104['(OP)'->'(OP)':notNull]) />
      </extractPlan>
      <filterPlan 2.0636 0,54/0,00 (v$1.name='scheduledSlot')>
        <filterPlan 2.0424 0,52/0,00 (v$1.doubleValue<=2850)>
          <filterPlan 2.0212 0,50/0,00 (v$1.state=0)>
            <extractPlan 2.0 0,48/0,00 v$1(ObjectProperty)>
              <boundPlan 0.0 0,00/0,00 v$1(ObjectProperty) bound(o.properties) />
            </extractPlan>
          </filterPlan>
        </filterPlan>
      </filterPlan>
    </multiVarPlan>
    <filterPlan 2.0636 0,54/0,00 (p2.name='scheduledEndSlot')>
      <filterPlan 2.0424 0,52/0,00 (p2.doubleValue>=2850)>
        <filterPlan 2.0212 0,50/0,00 (p2.state=0)>
          <extractPlan 2.0 0,48/0,00 p2(ObjectProperty)>
            <boundPlan 0.0 0,00/0,00 p2(ObjectProperty) bound(o.properties) />
          </extractPlan>
        </filterPlan>
      </filterPlan>
    </filterPlan>
  </multiVarPlan>
  <filterPlan 2.0424 0,52/0,00 (p1.name='noCapacityLoad')>
    <filterPlan 2.0212 0,50/0,00 (p1.doubleValue=0)>
      <extractPlan 2.0 0,48/0,00 p1(ObjectProperty)>
        <boundPlan 0.0 0,00/0,00 p1(ObjectProperty) bound(o.properties) />
      </extractPlan>
    </filterPlan>
  </filterPlan>
</multiVarPlan>
 

Remark:

with

- is there a possibility to give a hint not based on a string in the query plan but on the index itself ?

we do not mean the index of the query plan but the index itself (as we know pretty well which index would be fastest for a specific query)

Thanks, but this is the Explorer log and it includes the necessary XML presentation of the query plan only in 2.8.3, not in 2.7.6.

Therefore, you have to look for that information in 2.7.6 in the log file, after enabling logging, as explained in #9 above.

- is there a possibility to give a hint not based on a string in the query plan but on the index itself ?
- we do not mean the index of the query plan but the index itself (as we know pretty well which index would be fastest for a specific query)

Not directly, but the name of the index should also be included in the XML representation of the query plan, and you also have to specify a unique substring from the query plan XML, so specifying the index name should work.

find attached the log of 2.7

Can you tell if this is the query plan that you want (the first in the file that you sent)? Can you see here the requested indexes?

If it is, then any unique substring from that query plan that doesn't depend on parameters should work.

<distinctPlan plan="distinct(p2:index(nds['scheduledEndSlot'_0_2850->'scheduledEndSlot'_0:notNull]) <x> o:filter(extract(o,index(-126[p2->p2])),(o.classIdentifier='(OP)')) <x> p1:filter(filter(extract(p1,type(ObjectProperty[set])),(p1.doubleValue=0)),(p1.name='noCapacityLoad')) <x> v$1:filter(index(nds['scheduledSlot'_0_null(excl)->'scheduledSlot'_0_2850:notNull]),(o.properties join v$1)))" eval="9.3135" group="distinct(o)" eval="[]">
        <multiVarPlan plan="p2:index(nds['scheduledEndSlot'_0_2850->'scheduledEndSlot'_0:notNull]) <x> o:filter(extract(o,index(-126[p2->p2])),(o.classIdentifier='(OP)')) <x> p1:filter(filter(extract(p1,type(ObjectProperty[set])),(p1.doubleValue=0)),(p1.name='noCapacityLoad')) <x> v$1:filter(index(nds['scheduledSlot'_0_null(excl)->'scheduledSlot'_0_2850:notNull]),(o.properties join v$1))" eval="9.0583">
            <multiVarPlan plan="p2:index(nds['scheduledEndSlot'_0_2850->'scheduledEndSlot'_0:notNull]) <x> o:filter(extract(o,index(-126[p2->p2])),(o.classIdentifier='(OP)')) <x> p1:filter(filter(extract(p1,type(ObjectProperty[set])),(p1.doubleValue=0)),(p1.name='noCapacityLoad'))" eval="7.0699">
                <multiVarPlan plan="p2:index(nds['scheduledEndSlot'_0_2850->'scheduledEndSlot'_0:notNull]) <x> o:filter(extract(o,index(-126[p2->p2])),(o.classIdentifier='(OP)'))" eval="4.9851">
                    <btreePlan plan="index(nds['scheduledEndSlot'_0_2850->'scheduledEndSlot'_0:notNull])" eval="0.982" variable="p2" />
                    <filterPlan plan="filter(extract(o,index(-126[p2->p2])),(o.classIdentifier='(OP)'))" eval="3.0212">
                        <extractPlan plan="extract(o,index(-126[p2->p2]))" eval="3.0" variable="o">
                            <btreePlan plan="index(-126[p2->p2])" eval="1.0" variable="o" />
                        </extractPlan>
                    </filterPlan>
                </multiVarPlan>
                <filterPlan plan="filter(filter(extract(p1,type(ObjectProperty[set])),(p1.doubleValue=0)),(p1.name='noCapacityLoad'))" eval="1.0424">
                    <filterPlan plan="filter(extract(p1,type(ObjectProperty[set])),(p1.doubleValue=0))" eval="1.0212">
                        <extractPlan plan="extract(p1,type(ObjectProperty[set]))" eval="1.0" variable="p1">
                            <btreePlan plan="type(ObjectProperty[set])" eval="1.0" variable="p1" />
                        </extractPlan>
                    </filterPlan>
                </filterPlan>
            </multiVarPlan>
            <filterPlan plan="filter(index(nds['scheduledSlot'_0_null(excl)->'scheduledSlot'_0_2850:notNull]),(o.properties join v$1))" eval="0.9942">
                <btreePlan plan="index(nds['scheduledSlot'_0_null(excl)->'scheduledSlot'_0_2850:notNull])" eval="0.973" variable="v$1" />
            </filterPlan>
        </multiVarPlan>
    </distinctPlan>

yes, i.e.:

p2:index(nds['scheduledEndSlot'

we tried with 

... [[objectdb.query-plan-text=p2:index(nds['scheduledEndSlot']]

but no effect

Maybe the XML presentation is a bit different in 2.8.3, so the string has to be adjusted. Can you find this query plan in the log file when using 2.8.3 (not as the first and selected query plan, but as one of the candidate query plans later in the XML)?

If not, is it possible to reproduce it using coresystemdb.rar from November 2019? with which query and parameters exactly?

Yes you can use this db

just use the query above:

SELECT DISTINCT o from ObjectNode o join o.properties p1 join o.properties p2 where o.classIdentifier = '(OP)' and ((o.properties.name = 'scheduledSlot' and o.properties.doubleValue <= 2850 and o.properties.state = 0) and (p2.name = 'scheduledEndSlot' and p2.doubleValue >= 2850 and p2.state = 0) and (p1.name = 'noCapacityLoad' and p1.doubleValue = 0))

Exploring this issue further shows that the existing query hints ("objectdb.query-plan-pos", "objectdb.query-plan-text") cannot be useful in this case. The reason is that these hints are only useful for selecting a query plan from the final candidate query plans, but unfortunately in this case the specific query plan is filtered in an earlier stage (filtering is required because of the huge number of possible query plans).

A new version of ObjectDB 2.8.3_02 introduces a new query hint that you can try in explorer by adding to your query:

    [[objectdb.query-plan-var-order=p2,o,v$1,p1]]

This hint enforces selecting a query plan that processes the query variables in a specific order. The order of variables above follows the query plan that works well in version 2.7.6. You must use a valid order, otherwise the query may not be executed well. Consider replacing the implicit v$1 variable with an explicit variable name (e.g. by using p1, p2, p3), to be on the safe side for possible future changes.

Please check this new query hint and provide feedback.

great, that works exactly the way we need it :)

Excellent. Note that by knowing your data and ordering the variables accordingly (or by trying different options), you may be able to find query plans that are more efficient than query plans in 2.7.6. The idea is to filter as much as possible, as early as possible.

unfortunately there are problems, the given order is in most of the cases not followed, example:

2.8.3_02

SELECT DISTINCT o from ObjectNode o join o.properties p1 join o.properties p2 join o.properties p3 where o.classIdentifier = '(OP)' and ((p1.name = 'scheduledSlot' and p1.doubleValue <= 2603 and p1.state = 0) and (p2.name = 'scheduledEndSlot' and p2.doubleValue >= 2603) and (p3.name = 'noCapacityLoad' and p3.doubleValue = 0))  [[objectdb.query-plan-var-order=p1]]

execution time around 60 seconds

Query plan 1/16 description
============================

Step 1: Process ObjectNode (o) instances
----------------------------------------
[Step 1a]
Scan index com.agile.hummingbird.ObjectNode[classIdentifier]
locating ObjectNode (o) instances that satisfy: 
  (o.classIdentifier='(OP)').

[Step 1b]
Retrieve fields in ObjectNode (o) instances.

Step 2: Process ObjectProperty (p3) instances
  (for every result of step 1)
---------------------------------------------
[Step 2a]
Iterate over all the instances (p3) in o.properties.

[Step 2b]
Retrieve fields in ObjectProperty (p3) instances.

[Step 2c]
Filter the results of step 2b
retaining only results that satisfy:
  (p3.doubleValue=0).

[Step 2d]
Filter the results of step 2c
retaining only results that satisfy:
  (p3.name='noCapacityLoad').

Step 3: Process ObjectProperty (p2) instances
  (for every result tuple of the steps above)
---------------------------------------------
[Step 3a]
Iterate over all the instances (p2) in o.properties.

[Step 3b]
Retrieve fields in ObjectProperty (p2) instances.

[Step 3c]
Filter the results of step 3b
retaining only results that satisfy:
  (p2.doubleValue>=2603).

[Step 3d]
Filter the results of step 3c
retaining only results that satisfy:
  (p2.name='scheduledEndSlot').

Step 4: Process ObjectProperty (p1) instances
  (for every result tuple of the steps above)
---------------------------------------------
[Step 4a]
Iterate over all the instances (p1) in o.properties.

[Step 4b]
Retrieve fields in ObjectProperty (p1) instances.

[Step 4c]
Filter the results of step 4b
retaining only results that satisfy:
  (p1.state=0).

[Step 4d]
Filter the results of step 4c
retaining only results that satisfy:
  (p1.doubleValue<=2603).

[Step 4e]
Filter the results of step 4d
retaining only results that satisfy:
  (p1.name='scheduledSlot').

Step 5: Apply selection
-----------------------
Apply selection and prepare final results.

<multiVarPlan 15.3075 4,11/3,03 o,p1,p2,p3>
  <multiVarPlan 11.1804 3,97/3,03 o,p2,p3>
    <multiVarPlan 7.0956 3,76/3,03 o,p3>
      <extractPlan 3.0109 3,34/3,03 o(ObjectNode)>
        <btreePlan 1.0109 1,49/3,03 o(ObjectNode) index(-109['(OP)'->'(OP)':notNull]) />
      </extractPlan>
      <filterPlan 2.0424 0,52/0,00 (p3.name='noCapacityLoad')>
        <filterPlan 2.0212 0,50/0,00 (p3.doubleValue=0)>
          <extractPlan 2.0 0,48/0,00 p3(ObjectProperty)>
            <boundPlan 0.0 0,00/0,00 p3(ObjectProperty) bound(o.properties) />
          </extractPlan>
        </filterPlan>
      </filterPlan>
    </multiVarPlan>
    <filterPlan 2.0424 0,52/0,00 (p2.name='scheduledEndSlot')>
      <filterPlan 2.0212 0,50/0,00 (p2.doubleValue>=2603)>
        <extractPlan 2.0 0,48/0,00 p2(ObjectProperty)>
          <boundPlan 0.0 0,00/0,00 p2(ObjectProperty) bound(o.properties) />
        </extractPlan>
      </filterPlan>
    </filterPlan>
  </multiVarPlan>
  <filterPlan 2.0636 0,54/0,00 (p1.name='scheduledSlot')>
    <filterPlan 2.0424 0,52/0,00 (p1.doubleValue<=2603)>
      <filterPlan 2.0212 0,50/0,00 (p1.state=0)>
        <extractPlan 2.0 0,48/0,00 p1(ObjectProperty)>
          <boundPlan 0.0 0,00/0,00 p1(ObjectProperty) bound(o.properties) />
        </extractPlan>
      </filterPlan>
    </filterPlan>
  </filterPlan>
</multiVarPlan>

2.7.6

SELECT DISTINCT o from ObjectNode o join o.properties p1 join o.properties p2 join o.properties p3 where o.classIdentifier = '(OP)' and ((p1.name = 'scheduledSlot' and p1.doubleValue <= 2603 and p1.state = 0) and (p2.name = 'scheduledEndSlot' and p2.doubleValue >= 2603) and (p3.name = 'noCapacityLoad' and p3.doubleValue = 0))

execution time around 4 seconds

Query Plan Description
======================

Step 1: Process ObjectProperty (p1) instances
---------------------------------------------
Scan index com.agile.hummingbird.ObjectProperty[name, state, doubleValue]
locating ObjectProperty (p1) instances that satisfy: 
  and(and((p1.name='scheduledSlot'),(p1.state=0)),(p1.doubleValue<=2603)).

Step 2: Process ObjectNode (o) instances
  (for every result of step 1)
----------------------------------------
[Step 2a]
Scan index com.agile.hummingbird.ObjectNode[properties]
locating ObjectNode (o) instances that satisfy: 
  (o.properties join p1).

[Step 2b]
Retrieve fields in ObjectNode (o) instances.

[Step 2c]
Filter the results of step 2b
retaining only results that satisfy:
  (o.classIdentifier='(OP)').

Step 3: Process ObjectProperty (p3) instances
  (for every result tuple of the steps above)
---------------------------------------------
[Step 3a]
Scan type com.agile.hummingbird.ObjectProperty
locating ObjectProperty (p3) instances that satisfy: 
  (o.properties join p3).

[Step 3b]
Evaluate fields in ObjectProperty (p3) instances.

[Step 3c]
Filter the results of step 3b
retaining only results that satisfy:
  (p3.doubleValue=0).

[Step 3d]
Filter the results of step 3c
retaining only results that satisfy:
  (p3.name='noCapacityLoad').

Step 4: Process ObjectProperty (p2) instances
  (for every result tuple of the steps above)
---------------------------------------------
[Step 4a]
Scan type com.agile.hummingbird.ObjectProperty
locating ObjectProperty (p2) instances that satisfy: 
  (o.properties join p2).

[Step 4b]
Evaluate fields in ObjectProperty (p2) instances.

[Step 4c]
Filter the results of step 4b
retaining only results that satisfy:
  (p2.doubleValue>=2603).

[Step 4d]
Filter the results of step 4c
retaining only results that satisfy:
  (p2.name='scheduledEndSlot').

Step 5: Group results
---------------------
Group the results of step 4 by:
  o.

[Step 6]
Remove duplicates from the groups of step 5.

[Step 7]
Apply selection and prepare final results.
 

a remark, if the order is completely written down (i.e. p1,o,p2,p3) it is even worse

Regarding: [[objectdb.query-plan-var-order=p1]]
It expects to do nothing, because only the order of the listed variables is preserved.

Regarding: [[objectdb.query-plan-var-order=p1,o,p2,p3]]
Just checked and the order is preserved as expected. However, the order is not everything, because for the same order there are still different options regarding which indexes to use, etc. Therefore, you may need also to add a hint of the old type in addition to the order of variables. If you cannot find a way for this to work, please post the relevant XML from 2.7.6 and the string hint that you are trying to add.

we did now create statistics and used [[objectdb.query-plan-var-order=p1,o,p2,p3]] and it used the order, can we assume that when the statics was created the situation should/could be like this ?

Possibly, although the order should be respected also with no statistics, but with no statistics the order may not be sufficient for selecting a good query plan with that order.