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