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 < [email protected]> ha scritto: > Hi Luigi, > > for query: > SELECT FROM ANYVERTEX WHERE date < 999999999999999 ORDER BY date DESC > LIMIT 3 > > please find the describe 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 > > > On Tuesday, May 7, 2019 at 12:11:52 PM UTC+2, Luigi Dell'Aquila wrote: >> >> 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 < >> [email protected]> 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 < >>>> [email protected]> 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 [email protected]. >>>>> 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 [email protected]. >>> To view this discussion on the web visit >>> https://groups.google.com/d/msgid/orient-database/05b6c195-9738-41fd-bf63-487336da4041%40googlegroups.com >>> <https://groups.google.com/d/msgid/orient-database/05b6c195-9738-41fd-bf63-487336da4041%40googlegroups.com?utm_medium=email&utm_source=footer> >>> . >>> 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 [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/orient-database/b0133f14-9cc7-447f-8160-ff6081042012%40googlegroups.com > <https://groups.google.com/d/msgid/orient-database/b0133f14-9cc7-447f-8160-ff6081042012%40googlegroups.com?utm_medium=email&utm_source=footer> > . > 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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/orient-database/CAFZLH8mHaeZrSBiayYhCChHT_82A-mBQNKFOG-aL-BgWC1GFPw%40mail.gmail.com. For more options, visit https://groups.google.com/d/optout.
