708 words

composite index not used in query

#1
2016-04-20 13:13

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

hgzwicker
hgzwicker's picture
Joined on 2014-04-09
User Post #15
#2
2016-04-20 16:24

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)
support
support's picture
Joined on 2010-05-03
User Post #2,498
#3
2016-04-20 17:29

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.

 

 

 

hgzwicker
hgzwicker's picture
Joined on 2014-04-09
User Post #16
#4
2016-04-20 21:05

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)
support
support's picture
Joined on 2010-05-03
User Post #2,499
#5
2016-04-21 08:38

the sequence of the index members is the key, thanks

hgzwicker
hgzwicker's picture
Joined on 2014-04-09
User Post #17

Post Reply

Please read carefully the posting instructions - before posting to the ObjectDB website.

  • You may have to disable pop up blocking in order to use the toolbar (e.g. in Chrome).
  • Use ctrl + right click to open the browser context menu in the editing area (e.g. for using a browser spell checker).
  • To insert formatted lines (e.g. Java code, stack trace) - select a style in the toolbar and then insert the text in the new created block.
  • Avoid overflow of published source code examples by breaking long lines.
  • You may mark in paragraph code words (e.g. class names) with the code style (can be applied by ctrl + D).
  • Long stack traces (> 50 lines) and complex source examples (> 100 lines) should be posted as attachments.
Attachments:
Maximum file size: 32 MB
Cancel