possible index required for improving query performance

#1

Hi,

I have the following entity class:

@Entity

@Table
@Indices({
@Index(members={"relationshipType", "party"}),
@Index(members={"relationshipType", "otherParty"}),
    @Index(members={"relationshipType"})
})
@XmlRootElement
public class Relationship implements Comparable<Relationship> {

    public static enum RelationshipType {
        PARENT, OWNER, ADMIN, CONTAINS, MEMBER, EMPLOYEE, MANAGER
    }
   
    private BaseObject party;
    private BaseObject otherParty;

    @Enumerated(EnumType.STRING)
    private RelationshipType relationshipType;

}

With the following query:

TypedQuery<BaseObject> query = em().createQuery(
    "SELECT r.party FROM Relationship r WHERE r.otherParty = :otherParty_id AND r.party.subclassName = :pclass AND r.relationshipType=RelationshipType."+type.toString(), BaseObject.class);
 
  query.setParameter("otherParty_id", otherParty);
  query.setParameter("pclass", partyClass);

We have noticed that this query slows down in a linear way as the number of relationships and BaseObjects grow.

The query is using two indexed fields (otherParty and relationshipType) but subclassName is not indexed, would that be the cause for the slow query?

Is there any other index that should be added to make this query faster?

 

Thanks in advance,

Eitan

 

#2

Try adding 2 new indexes:

  • an index on subclassName in BaseObject.
  • an index on party in Relationship (or switch the order of party and relationshipType in the existing index).

Notice that a composite index can be used as a single index of the first element (with a small penalty compared to a dedicated simple index) but not of the second or third element, so the order of elements in a composite index makes a difference.

ObjectDB Support
#3

Hi,

I've been actually dealing with this, so it would be best if I communicate directly.

Thank you for your advice, putting the party first in the index definition did help. However, it did not work until I removed the index on relationshipType. I've tried it the other way as well - added the relationshipType index back and started a test with an empty database. It slowed down significantly after a few thousands records were added. I'm quite happy to remove this index as we don't really need it anyway, but this leads to a couple of questions:

1. How does objectdb select which index to use when there are multiple indexes? In this scenario we have a composite index on 2 fields and a single index on one of them. Which index will it pick?

2. How do we remove this index from an existing database? I've seen a post about Index Rebuilder on the forum that talks about adding new indexes. As I understand, we'd need to stop the DB, run the Doctor in repair mode and start the DB with the repaired file before accessing it with the code containing new indexes. Will this procedure also remove the old indexes?

Thanks and regards,

Natalia.

#4
  1. ObjectDB prepares many candidate query plans and eventually selects the one that is evaluated as the best. From your description I understand that the query plan that was selected in this specific case wasn't the best. You may upload the database and post the exact query details and this will be checked.
  2. Just remove the index from your source code and use the class again with ObjectDB. Running the Doctor will reclaim the space that the index takes, but it is only required after adding new indexes.
ObjectDB Support
#5

The exact query is:

SELECT r.party FROM Relationship r
WHERE r.otherParty = :otherParty AND
      r.relationshipType=RelationshipType.OWNER AND
      r.party.subclassName = 'PDI'

otherParty is an object, so I can't run this query in Explorer with a non-null parameter. If I run it with null, I can see in Explorer that it looks at the relationshipType index first and in this case it does not give much advantage.

I've uploaded the DB to your site, it's called contextspace.odb.zip.

Regards,

Natalia.

#6

You should be able to use object parameters in the Explorer, as explained in the manual:

In the [Parameters] table provide arguments for parameters (if any). An entity object can be specified by type and primary key separated by # (e.g. Point#1). A collection can be specified as a comma separated list of elements.

It will be easier to explore this if the query can be run in the Explorer, so please suggest parameters.

ObjectDB Support
#7

You can use CDI#197240 as a parameter.

Regards,

Natalia.

#8

Thank you for this example. Composite indexes that cover more than one relevant field have been underestimated by the query planner. This is now fixed in build 2.3.7_01.

ObjectDB Support

Reply