massive performance issues

we are fighting more or less since version 2.9 with the performance of our systems. We are completely unclear if indexes or statistics are really used. 

sometimes a query like this takes takes around 500 ms, sometimes it takes 20 seconds 

select distinct o from ObjectNode o join o.properties p1 join o.properties p10 join o.properties p20 join o.properties p21 where o.parentNode.classIdentifier = '(PD)' and o.classIdentifier = '(OP)' and (o.linkedObjects.classIdentifier = '(TC)' and o.linkedObjects.objectNameUpper = 'ASSEMBLY-ENGINE' and o.linkedObjects.type = 1 and o.linkedObjects.state = 0) and (((p1.name = 'scheduledSlot' and p1.doubleValue <= 2898)) and (p10.name = 'scheduledState' and p10.doubleValue = 2) and (o.properties.name = 'feedbackState' and o.properties.doubleValue != 13) and (p21.name = 'globalFeedback' and p21.doubleValue = 0) and (p20.name = 'roughPlanning' and p20.doubleValue = 0))
  

attached you find  

- odb20250725.log 

>>> this is after doctor and running statistics. You can see that suddenly on starting some index is cleared (doctor should correctly create that ???) and then there is a mixture of creating indexes and statistics 

- ISAR-before-doctor.odb.zip, the initial database   

- ISAR-after-doctor.odb.zip, the database after doctor 

- ISAR-after-statistics.odb.zip, the database after statistics 

https://www.dropbox.com/scl/fi/gqfubwbpnqmmi2lq0cjnd/odb20250725.log?rlkey=v68xvvd7eiovhtbstrzcj9vjs&dl=0 

https://www.dropbox.com/scl/fi/uk183qekfemvjogjv2ys2/ISAR-after-doctor.odb.zip?rlkey=x84wu8ewtwz4en1wvjddwomze&dl=0 

https://www.dropbox.com/scl/fi/29j4w5mym7ydbyavfpf9m/ISAR-after-statistics.odb.zip?rlkey=vhhn84apfz976cjdbg1deh3xd&dl=0 

https://www.dropbox.com/scl/fi/0q04cot7u50s7nghafq3r/ISAR-before-doctor.odb.zip?rlkey=s2en8w2ok7h0rbavago41pbds&dl=0

In order to examine the issue, please provide a database on which that query, when running in the the Explorer, is very slow. Is any or all the databases that you made available can demonstrate the slow query?

see all the links that we provided in #1, also the description and logs of all these problems

Sure, but you wrote:

> sometimes a query like this takes takes around 500 ms, sometimes it takes 20 seconds

Can you confirm that the provided databases demonstrate the ~20 seconds in a consistent way? It is just not clear from post #1.

the problem is

- we do not understand if the statistics/indexes are really used (see problem with the deleting of indexes and mixed index/statistics creating what can be seen in the log)

- the behaviour 500 ms versus 20 seconds is completely random and not predictable/reproducable