Huge fetchingFromTargetElapsed

[heichwald]

OrientDB v.2.0.7

Configuration: 2GB Heap, 3GB Disk Cache AMD-FX 8150 8 cores, SSD drive

One cluster called Article contains around 500 000 records
Getting one record is reasonably fast:

explain select from Article where @rid <> #13:250000 limit 1

Profiled command '{current:#13:0,evaluated:1,fetchingFromTargetElapsed:0,documentReads:1,documentAnalyzedCompatibleClass:1,recordReads:1,elapsed:0.237803,resultType:collection,resultSize:1}' in 0.003000 sec(s):
{"@type":"d","@version":0,"current":"#13:0","evaluated":1,"fetchingFromTargetElapsed":0,"documentReads":1,"documentAnalyzedCompatibleClass":1,"recordReads":1,"elapsed":0.237803,"resultType":"collection","resultSize":1,"@fieldTypes":"current=x,evaluated=l,fetchingFromTargetElapsed=l,documentReads=l,documentAnalyzedCompatibleClass=l,recordReads=l,elapsed=f"}

Now I I created an index on a date field called publishDate, the same query is really slow while it evaluates 1 record, the fetching time is huge, and I can run the query several times it does not go down.

orientdb {db=Analytics}> explain select from indexvaluesasc:Article.publishDate where @rid <> #13:250000 limit 1 

Profiled command '{current:#13:320240,evaluated:1,fetchingFromTargetElapsed:421,documentReads:1,recordReads:1,elapsed:421.50885,resultType:collection,resultSize:1}' in 0.425000 sec(s):
{"@type":"d","@version":0,"current":"#13:320240","evaluated":1,"fetchingFromTargetElapsed":421,"documentReads":1,"recordReads":1,"elapsed":421.50885,"resultType":"collection","resultSize":1,"@fieldTypes":"current=x,evaluated=l,fetchingFromTargetElapsed=l,documentReads=l,recordReads=l,elapsed=f"}
orientdb {db=Analytics}> 

[andrii0lomakin]

Hi, it is not a bug. You run query which uses @Rid <> #13:250000 it means that you will iterate overall index to find this value. Index can be considered as list sorted by key so to find index value (@Rid) you need to iterate over all index entries.

[heichwald]

Hi,

but I don't get it. It could just look at the first element in the index and see that the rid are different, why does it need to iterate over all entries? Couldn't it just retrieve the first one? or by batch of a few entries?
Does it mean here that it will load 500 000 records to just scan one and see that the first one has a different rid?

How is it different when not loading from the index but from Article directly, why is it fast in this case?

[heichwald]

How could I solve this simple use case, get the first element sorted by date with the different rid condition?, other dbs like mongo can give me this result in 5ms or less

[heichwald]

BTW, I don't say it is a bug, but something which should be improved in the future if possible.

[heichwald]

Example in Mongo with just a index on publishDate on the same "cluster" would be
db.articles.find({'canonicalUrl':{$ne:'http://nymag.com/thecut/2013/09/watch-the-cushnie-et-ochs-show-live.html'}}).sort({'publishDate':-1}).limit(1).explain()

I used canonicalUrl here to simulate @Rid <>

took 4ms the first time and 1ms on subsequent queries

[andrii0lomakin]

I think you are right let me fix it .

[andrii0lomakin]

It is already fixed in 2.1 there is distribution to test https://www.dropbox.com/s/xs2w59sncv9ln4m/orientdb-community-2.1-SNAPSHOT-distribution.zip?dl=0 we will provide official release candidate during couple of days.