Home » Support » Forum » composite index not used in query

composite index not used in query

#1

we defined a composite index for class Action:

@Index(name="ssst",members={"startDate","state","subType","type"})

and an index for the OneToMany

@OneToMany(fetch=FetchType.LAZY)
@Index 
public List<ObjectNode> objectsInCharge = new ArrayList<ObjectNode>();

on running a query like

select count(a) from Action a JOIN a.objectsInCharge a1
WHERE ((a1.classIdentifier = '(OP)' and (a1.childNodes.classIdentifier = '(TC)' and
        a1.childNodes.objectNameUpper = 'POSTPROCESSING')) and
        (a.state = 3 and a.subType = 3 and a.type = 0 and a.startDate > ?1))

the composite index is not used, query plan: 

Step 1: Process Action (a) instances
------------------------------------
[Step 1a]
Scan index com.agile.hummingbird.Action[startDate]
locating all the Action (a) instances.

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

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

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

[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.state=3).

Step 2: Process ObjectNode (a1) instances
  (for every result of step 1)
-----------------------------------------
[Step 2a]
Iterate over all the instances (a1) in a.objectsInCharge.

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

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

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 a1.childNodes.

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

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

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

Step 4: Group results
---------------------
Evaluate aggregate expressions:
  count(a).

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

Is there something wrong in the definition (remark: the order of the arguments in the query does not make any difference) ?

#2

Please try to reduce the problem to the possible minimum, removing all unnecessary components.

For example, try this query:

SELECT count(a) FROM Action a
WHERE a.state = 3 AND a.subType = 3 AND a.type = 0 AND a.startDate > ?1

If the composite index was added to an existing database and you are using a version of ObjectDB before 2.6.6, you have to rebuild the database with the Doctor in order to make the new index active.

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

stepping up with the complexity, the results are (what confuses me is that the more elements I add, the less index seems to be used):

select a from Action a
where (a.state = 1 and a.type = 0 and a.subType = 1)
Query Plan Description
======================

[Step 1]
Scan index com.agile.hummingbird.Action[state, subType, type]
locating Action (a) instances that satisfy:
  and(and((a.state=1),(a.subType=1)),(a.type=0)).

[Step 2]
Apply selection and prepare final results.

 

select a from Action a
where (a.startDate >= ?1 and a.state = 1 and a.type = 0 and a.subType = 1)
Query Plan Description
======================

[Step 1]
Scan index com.agile.hummingbird.Action[startDate, state, subType, type]
locating all the Action (a) instances.

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

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

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

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

[Step 6]
Apply selection and prepare final results.

 

select a from Action a
where (a.startDate > ?1 and a.state = 1 and a.type = 0 and a.subType = 1) and
          (a.objectsInCharge.objectNameUpper like '%CAM%')

 

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

Step 1: Process Action (a) instances
------------------------------------
[Step 1a]
Scan index com.agile.hummingbird.Action[state, subType, type]
locating all the Action (a) instances.

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

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

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

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

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

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

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

[Step 2c]
Filter the results of step 2b
retaining only results that satisfy:
  like(v$1.objectNameUpper,'%CAM%').

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

 

 

 

#4

In all the new examples a composite index is used.

Can you change the order of the fields in the composite index:

@Index(name="ssst",members={"state","subType","type", "startDate"})

or any other order in which startDate is the last?

In the expression:

    a.state = 3 AND a.subType = 3 AND a.type = 0 AND a.startDate > ?1

The startDate constraint makes the composite index less useful, unless startDate is last.

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

the sequence of the index members is the key, thanks


Post Reply

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