We have an entity that has indexes on several fields, including some composite indexes. When I make a query for only one field, it takes 0-3 milliseconds:
SELECT p FROM RackPlanogram p WHERE p.rackOID = "826268" Query plan 1/2 description ============================ [Step 1] Scan index eu.extech.quant.data.planogram_lifecycle.RackPlanogram[rackOID, categoryOID] locating RackPlanogram (p) instances that satisfy: (p.rackOID='826268'). [Step 2] Apply selection and prepare final results. <btreePlan 1.0089 0,70/2,71 p(RackPlanogram) index(-210['826268'->'826268'*:notNull]) />
However, when I make a query for two fields, ObjectDB does a scan of both indexes and merges the results, which is orders of magnitude slower (30-50 ms):
SELECT p FROM RackPlanogram p WHERE p.rackOID = "826268" AND p.deleted = FALSE Query plan 1/2 description ============================ [Step 1] Scan index eu.extech.quant.data.planogram_lifecycle.RackPlanogram[deleted] locating RackPlanogram (p) instances that satisfy: (p.deleted=false). [Step 2] Scan index eu.extech.quant.data.planogram_lifecycle.RackPlanogram[rackOID, categoryOID] locating RackPlanogram (p) instances that satisfy: (p.rackOID='826268'). [Step 3] Order the results of step 2 by: p. [Step 4] Merge the results of steps 1, 3 using AND. [Step 5] Apply selection and prepare final results. <mergePlan and 10.0377 2,34/2,71 11> <btreePlan 9.9237 0,70/2,71 p(RackPlanogram) index(-108[false->false:notNull]) /> <orderPlan 2.9119 1,43/2,71 p> <btreePlan 1.0089 0,70/2,71 p(RackPlanogram) index(-210['826268'->'826268'*:notNull]) /> </orderPlan> </mergePlan>
Is there a way to stop ObjectDB from doing multiple index scans, and only pick the best single index with highest selectivity, which in this case is [rackOID, categoryOID]?
The "deleted" flag has very low selectivity, so its index should never be used in a query if there is a better index available. The [rackOID, categoryOID] index will yield maybe 0-10 results that ObjectDB could simply filter afterwards, but instead, ObjectDB is doing an intersection between an index that yields 10 results, and the [deleted] index that yields hundreds of thousands of results, and wasting a lot of time.
We need a general solution for all queries, because we have many entities with an index on the "deleted" flag so that we can find deleted entities quickly, but >99% of queries include a "deleted = FALSE" condition, which appears to be slowing essentially every query down by 10x or more. The only workaround we have right now is to remove "deleted = FALSE" from the query, and do the filtering in our application.
Note: I know the ObjectDB version I reproduced this issue on is fairly old. I tried downloading 2.9.5 to see if this issue still exists, but I couldn't get Explorer to launch using the provided script:
~/Downloads/objectdb-2.9.5/bin $ ./explorer.sh
Exception in thread "AWT-EventQueue-0" java.lang.NoClassDefFoundError: javax/jdo/JDODataStoreException
at com.objectdb.Explorer$1.run(Explorer.java:57)
at java.desktop/java.awt.event.InvocationEvent.dispatch(InvocationEvent.java:323)
at java.desktop/java.awt.EventQueue.dispatchEventImpl(EventQueue.java:723)
at java.desktop/java.awt.EventQueue.dispatchEvent(EventQueue.java:702)
at java.desktop/java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:203)
at java.desktop/java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:124)
at java.desktop/java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:113)
at java.desktop/java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:109)
at java.desktop/java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:101)
at java.desktop/java.awt.EventDispatchThread.run(EventDispatchThread.java:90)
Caused by: java.lang.ClassNotFoundException: javax.jdo.JDODataStoreException
at java.base/jdk.internal.loader.BuiltinClassLoader.loadClass(BuiltinClassLoader.java:580)
at java.base/java.lang.ClassLoader.loadClass(ClassLoader.java:490)
... 10 more 