OrientDB query to receive last vertex before a given date

[Jon]

Let's say I have the following list of vertices (connected by edges) in the orient database:

 [t=1] --> [t=2] --> [t=3] --> [t=4] --> [t=5] --> [t=6] --> [t=7]

Each vertex has a timestamp t. I now want to receive the last vertex before a given date. Example: give me the last vertex before t=5, which is t=4.

Currently I'am using the following query to do this:

SELECT FROM ANYVERTEX WHERE t < 5 ORDER BY t DESC LIMIT 1

This is working fine when having up to let's say 1000 elements but the performance of that query drops with the number of elements inserted in the list. I already tried using an index, which improved the overall performance, but the problem, that the performance drops with the amount of elements still persists.

[Luigi Dell'Aquila]

Hi Jon

Using an index should definitely solve the problem, so it's strange that it didn't work in your case.

Which OrientDB version are you using? And what kind of index did you define?

Thanks

Luigi

--

---
You received this message because you are subscribed to the Google Groups "OrientDB" group.
To unsubscribe from this group and stop receiving emails from it, send an email to orient-databa...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Jon]

Hi Luigi,

thanks for your fast reply. I' am doing performance tests in OrientDB 3.0.18 locally and 3.0.3 in our test environment. The index type is UNIQUE(SBTREE). The index is a composite key composed of an id and the timestamp. From the logs I can definitely confirm that the query above takes the most of the time (per attached vertex) of all remaining operations (like attaching edge etc.).  In orient studio if I execute:

SELECT FROM ANYVERTEX WHERE t < 99999999999 AND id=... ORDER BY t DESC LIMIT 1

it takes 0.133 sec (which improves with caching) and with

SELECT FROM ANYVERTEX WHERE t < 1 AND id=... ORDER BY t DESC LIMIT 1

only 0.054.

Thanks,

Jon

To unsubscribe from this group and stop receiving emails from it, send an email to orient-...@googlegroups.com.

[Luigi Dell'Aquila]

Hi Jon,

Could you please post an EXPLAIN of that query?

Thanks

Luigi

Il giorno mar 7 mag 2019 alle ore 11:23 'Jon' via OrientDB <orient-...@googlegroups.com> ha scritto:

Hi Luigi,

thanks for your fast reply. I' am doing performance tests in OrientDB 3.0.18 locally and 3.0.3 in our test environment. The index type is UNIQUE(SBTREE). The index is a composite key composed of an id and the timestamp. From the logs I can definitely confirm that the query above takes the most of the time (per attached vertex) of all remaining operations (like attaching edge etc.).  In orient studio if I execute:

SELECT FROM ANYVERTEX WHERE t < 99999999999 AND id=... ORDER BY t DESC LIMIT 1

it takes 0.133 sec (which improves with caching) and with

SELECT FROM ANYVERTEX WHERE t < 1 AND id=... ORDER BY t DESC LIMIT 1

only 0.054. 

On Tuesday, May 7, 2019 at 8:44:54 AM UTC+2, Luigi Dell'Aquila wrote:

Hi Jon

Using an index should definitely solve the problem, so it's strange that it didn't work in your case.

Which OrientDB version are you using? And what kind of index did you define?

Thanks

Luigi

Il giorno lun 6 mag 2019 alle ore 13:44 'Jon' via OrientDB <orient-...@googlegroups.com> ha scritto:

Let's say I have the following list of vertices (connected by edges) in the orient database:

 [t=1] --> [t=2] --> [t=3] --> [t=4] --> [t=5] --> [t=6] --> [t=7]

Each vertex has a timestamp t. I now want to receive the last vertex before a given date. Example: give me the last vertex before t=5, which is t=4.

Currently I'am using the following query to do this:

 
SELECT FROM ANYVERTEX WHERE t < 5 ORDER BY t DESC LIMIT 1

This is working fine when having up to let's say 1000 elements but the performance of that query drops with the number of elements inserted in the list. I already tried using an index, which improved the overall performance, but the problem, that the performance drops with the amount of elements still persists.

--

---
You received this message because you are subscribed to the Google Groups "OrientDB" group.
To unsubscribe from this group and stop receiving emails from it, send an email to orient-...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

---
You received this message because you are subscribed to the Google Groups "OrientDB" group.

To unsubscribe from this group and stop receiving emails from it, send an email to orient-databa...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/orient-database/05b6c195-9738-41fd-bf63-487336da4041%40googlegroups.com.

[Jon]

Hi Luigi,

for query:

SELECT FROM ANYVERTEX WHERE date < 999999999999999 ORDER BY date DESC LIMIT 3

please find the explan output below.

+ FETCH FROM INDEX ANYVERTEX.date_targetId
  date < 999999999999999
+ EXTRACT VALUE FROM INDEX ENTRY
  filtering clusters [95,96,94,97,98,87,75,79,80,77,78,76,91,90,89,88,92,93]
+ FILTER ITEMS BY CLASS 
  ANYVERTEX
+ LIMIT ( LIMIT 3)

Thanks
Jon

To unsubscribe from this group and stop receiving emails from it, send an email to orient-...@googlegroups.com.

[Luigi Dell'Aquila]

Hi Jon,

It seems correct, there is no additional ORDER BY in the execution plan and the fetch from index is correct, so I'd say the problem is probably related to the index size or to some caching. 

In general, I'd say you can expect the performance to remain more or less constant around these values

Thanks

Luigi

Il giorno mar 7 mag 2019 alle ore 13:03 'Jon' via OrientDB <orient-...@googlegroups.com> ha scritto:

Hi Luigi,

for query:

SELECT FROM ANYVERTEX WHERE date < 999999999999999 ORDER BY date DESC LIMIT 3

please find the describe below.

To unsubscribe from this group and stop receiving emails from it, send an email to orient-databa...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/orient-database/b0133f14-9cc7-447f-8160-ff6081042012%40googlegroups.com.