query hint

some years ago there was a chat around query hints

http://www.objectdb.com/issue/2417

we are currently trying that with the latest version but we do not see that our hint is used

this is the query:

SELECT DISTINCT o from ObjectNode o join o.properties p1 join o.properties p2 join o.properties p3 join o.properties p4 where o.classIdentifier = '(OP)' and o.linkedObjects.classIdentifier = '(TC)' and o.linkedObjects.objectNameUpper = '3D-PRINT' and o.linkedObjects.state = 0 and o.linkedObjects.type = 1 and o.properties.name = 'roughPlanning' and o.properties.doubleValue = 1 and p1.name = 'globalFeedback' and p1.doubleValue = 0 and ((p2.name = 'scheduledSlot' and p2.doubleValue < 2795) and (p3.name = 'scheduledEndSlot' and p3.doubleValue >= 2795) and (p4.name = 'slots' and p4.doubleValue > 0))
[[objectdb.query-plan-text=index(ctso['(TC)'->'(TC)']]

but the index that is in the hint is not used

the MultiVarPlan that is listed shows our index in a filterPlan:

<filterPlan 2.0848 0,56/0,00 (v$1.classIdentifier='(TC)')>
            <filterPlan 2.0636 0,54/0,00 (v$1.objectNameUpper='3D-PRINT')>
              <filterPlan 2.0424 0,52/0,00 (v$1.state=0)>
                <filterPlan 2.0212 0,50/0,00 (v$1.type=1)>

This is the complete explorer output:

Query plan 1/128 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$2) instances
  (for every result of step 1)
----------------------------------------------
[Step 2a]
Iterate over all the instances (v$2) in o.properties.

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

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

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

Step 3: Process ObjectNode (v$1) instances
  (for every result tuple of the steps above)
------------------------------------------
[Step 3a]
Iterate over all the instances (v$1) in o.linkedObjects.

[Step 3b]
Retrieve fields in ObjectNode (v$1) instances.

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

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

[Step 3e]
Filter the results of step 3d
retaining only results that satisfy:
  (v$1.objectNameUpper='3D-PRINT').

[Step 3f]
Filter the results of step 3e
retaining only results that satisfy:
  (v$1.classIdentifier='(TC)').

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

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

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

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

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

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

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

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

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

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

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

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

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

[Step 7b]
Retrieve fields in ObjectProperty (p4) instances.

[Step 7c]
Filter the results of step 7b
retaining only results that satisfy:
  (p4.doubleValue>0).

[Step 7d]
Filter the results of step 7c
retaining only results that satisfy:
  (p4.name='slots').

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

<multiVarPlan 27.6031 4,00/2,65 o,p1,p2,p3,p4,v$1,v$2>
  <multiVarPlan 23.5183 3,93/2,65 o,p1,p2,p3,v$1,v$2>
    <multiVarPlan 19.4335 3,84/2,65 o,p1,p2,v$1,v$2>
      <multiVarPlan 15.3488 3,74/2,65 o,p2,v$1,v$2>
        <multiVarPlan 11.264 3,60/2,65 o,v$1,v$2>
          <multiVarPlan 7.0945 3,38/2,65 o,v$2>
            <extractPlan 3.0098 2,96/2,65 o(ObjectNode)>
              <btreePlan 1.0098 1,12/2,65 o(ObjectNode) index(-103['(OP)'->'(OP)':notNull]) />
            </extractPlan>
            <filterPlan 2.0424 0,52/0,00 (v$2.name='roughPlanning')>
              <filterPlan 2.0212 0,50/0,00 (v$2.doubleValue=1)>
                <extractPlan 2.0 0,48/0,00 v$2(ObjectProperty)>
                  <boundPlan 0.0 0,00/0,00 v$2(ObjectProperty) bound(o.properties) />
                </extractPlan>
              </filterPlan>
            </filterPlan>
          </multiVarPlan>
          <filterPlan 2.0848 0,56/0,00 (v$1.classIdentifier='(TC)')>
            <filterPlan 2.0636 0,54/0,00 (v$1.objectNameUpper='3D-PRINT')>
              <filterPlan 2.0424 0,52/0,00 (v$1.state=0)>
                <filterPlan 2.0212 0,50/0,00 (v$1.type=1)>
                  <extractPlan 2.0 0,48/0,00 v$1(ObjectNode)>
                    <boundPlan 0.0 0,00/0,00 v$1(ObjectNode) bound(o.linkedObjects) />
                  </extractPlan>
                </filterPlan>
              </filterPlan>
            </filterPlan>
          </filterPlan>
        </multiVarPlan>
        <filterPlan 2.0424 0,52/0,00 (p2.name='scheduledSlot')>
          <filterPlan 2.0212 0,50/0,00 (p2.doubleValue<2795)>
            <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.0424 0,52/0,00 (p1.name='globalFeedback')>
        <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>
    <filterPlan 2.0424 0,52/0,00 (p3.name='scheduledEndSlot')>
      <filterPlan 2.0212 0,50/0,00 (p3.doubleValue>=2795)>
        <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 (p4.name='slots')>
    <filterPlan 2.0212 0,50/0,00 (p4.doubleValue>0)>
      <extractPlan 2.0 0,48/0,00 p4(ObjectProperty)>
        <boundPlan 0.0 0,00/0,00 p4(ObjectProperty) bound(o.properties) />
      </extractPlan>
    </filterPlan>
  </filterPlan>
</multiVarPlan>
 

Query plans hints are still supported. However, since this is still an undocumented in-development feature, you may have to adjust your code when upgrading to new versions.

When you use the "objectdb.query-plan-text" hint the text you provide must match one of the query plans logging text. Logging format and text might have changed between versions, so the string "index(ctso['(TC)'->'(TC)']" that worked in previous versions may match nothing now.

To check the up to date logging text of query plans that used that index run the query with debug logging:

<general>
 ...
  <logger name="query" level="debug" />
</general>

You should see query plans that use the index, and by selecting text that is included in their logging text to promote their selection.

none of these indices is in the log:

@Indices({ 

@Index(name="ctso",members={"classIdentifier","type","state","objectNameUpper"}),

@Index(name="lctso",members={"linkedObjects.classIdentifier","linkedObjects.type","linkedObjects.state","linkedObjects.objectNameUpper"}),

@Index(name="ln",members={"linkedObjects.pointingTo.nodePath"})

})

the collection is defined as

@OneToMany(fetch=FetchType.LAZY)

@Index 

public List<ObjectNode> linkedObjects = new ArrayList<ObjectNode>();

the query is:

SELECT DISTINCT o from ObjectNode o join o.properties p1 join o.properties p2 join o.properties p3 join o.properties p4 where o.classIdentifier = '(OP)' and o.linkedObjects.classIdentifier = '(TC)' and o.linkedObjects.objectNameUpper = '3D-PRINT' and o.linkedObjects.state = 0 and o.linkedObjects.type = 1 and o.properties.name = 'roughPlanning' and o.properties.doubleValue = 1 and p1.name = 'globalFeedback' and p1.doubleValue = 0 and ((p2.name = 'scheduledSlot' and p2.doubleValue < 2795) and (p3.name = 'scheduledEndSlot' and p3.doubleValue >= 2795) and (p4.name = 'slots' and p4.doubleValue > 0))

and the log is, see attachment

If a query plan is not included in the debug log then hints cannot help.

If you can share a sample database + an exact query to run on that database, the issue will be investigated further.

here you can download the database

https://www.dropbox.com/scl/fi/ei23541z7wus4imwz622o/coreSystemDb.odb.zip?rlkey=k5nwyko8m10te6zq9dnljkchk&dl=0

the query is exactly the query mentioned above, there are no results for the query but don't care about that 

Due to some change in the query logging format, the new hint should be:

    index(-101:ctso['(TC)'_1_0_'3D-PRINT'->'(TC)'_1_0_'3D-PRINT':notNull])

or in a short form (for example):

    ctso['(TC)'

The -101 is an internal ID of the index.

You could not see the index in the log when the hint used mismatching text because of early filtering (there are too many combinations to analyze and present all). A possible way to inspect such cases and get more information on the indexes (e.g. their IDs) is to try shorter queries with only some of the components of the real queries.

Although with an up to date hint text ObjectDB does use the index on the sample database, it is not used in an optimal way.

To use it optimally you must also update the sample database statistics, by running a special query:

public class UpdateStat {
    public static void main(String[] args) throws Exception
    {
        EntityManagerFactory emf =
            Persistence.createEntityManagerFactory(args[0]);
        EntityManager em = emf.createEntityManager();

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

        em.close();
        emf.close();
    }
}

With this sample database after updating its statistics, that index is used correctly, with or without the hint.

great, thanks for the reply. Do we have to run the statistics just once or from time to time again ?

You have to run it after major data changes that can affect query plan selection.

It could be done as a background task (e.g. once every weekend).

In practice, running it once, when the database already contains sufficient data, may be sufficient.

thanks

just a remark, the query is still around 5 times slower than with version 2.7

update: no significant difference

Just to make sure, no difference between the two versions, i.e. #11 is an update to #10?

yes, everything fine