combined index not used

Type: BugVersion: 2.7.6Priority: NormalStatus: FixedReplies: 11
#1

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.

 

 

#2

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?

ObjectDB Support
ObjectDB - Fast Object Database for Java (JPA/JDO)
#3

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

#4

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

ObjectDB Support
ObjectDB - Fast Object Database for Java (JPA/JDO)
#5

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.

ObjectDB Support
ObjectDB - Fast Object Database for Java (JPA/JDO)
#6

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.

ObjectDB Support
ObjectDB - Fast Object Database for Java (JPA/JDO)
#7

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:

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:

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.

ObjectDB Support
ObjectDB - Fast Object Database for Java (JPA/JDO)
#8

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

#9

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>
ObjectDB Support
ObjectDB - Fast Object Database for Java (JPA/JDO)
#10

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).

ObjectDB Support
ObjectDB - Fast Object Database for Java (JPA/JDO)
#11

Thanks, question:

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

#12

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).

ObjectDB Support
ObjectDB - Fast Object Database for Java (JPA/JDO)

Post Reply

To post a reply and/or subscribe to update notifications - please login