Hi Garrett can you provide the explain of
explain select from Post order by updatedAt desc limit 10 with index created on Post class? Thanks Enrico 2014-07-28 7:37 GMT+02:00 Garrett Gottlieb <[email protected]>: > Looks like a potential bug with the abstract index. > > Building the index on Post reduced the query from 10 seconds to 0.017 > seconds: (explain included) > orientdb {pumpup}> CREATE INDEX Post.updatedAt ON Post (updatedAt) > NOTUNIQUE > > > > > Creating index... > Created index successfully with 2719297 entries in 168.957993 sec(s). > > > > > Index created successfully > orientdb {pumpup}> select from Post order by updatedAt desc limit 10 > > > > ----+---------+------+-------+--------+---------+---------+---------+---------+---------+---------+---------+---------+---------+----------------- > # |@RID |_allow|private|actionId|likeCount|objectId > |isOptimiz|commentCo|hashtagsP|createdAt|updatedAt|ACL |user > |dataCache > > ----+---------+------+-------+--------+---------+---------+---------+---------+---------+---------+---------+---------+---------+----------------- > 0 |#22:27...|[1] |false |1 |0 |CaWJkr...|true |0 > |true |2014-0...|2014-0...|{CMvKU...|{__typ...|{user={\"setti... > 1 |#22:27...|[1] |false |1 |0 |LFAaO2...|true |0 > |true |2014-0...|2014-0...|{*={re...|{__typ...|{user={\"setti... > 2 |#22:27...|[1] |false |1 |0 |CuTTUz...|true |0 > |true |2014-0...|2014-0...|{mcAlf...|{__typ...|{user={\"setti... > 3 |#22:27...|[1] |false |1 |0 |fdy9X2...|true |0 > |true |2014-0...|2014-0...|{*={re...|{__typ...|{user={\"setti... > 4 |#22:27...|[1] |false |1 |3 |fSyNBu...|true |0 > |true |2014-0...|2014-0...|{lM6Fp...|{__typ...|{user={\"setti... > 5 |#22:27...|[1] |false |1 |0 |h8ogaR...|true |0 > |true |2014-0...|2014-0...|{x3ikH...|{__typ...|{user={\"setti... > 6 |#22:27...|[1] |false |1 |0 |ew2bqg...|true |0 > |true |2014-0...|2014-0...|{YZHrd...|{__typ...|{user={\"setti... > 7 |#22:27...|[1] |false |1 |0 |crlcnr...|true |0 > |true |2014-0...|2014-0...|{HYVI5...|{__typ...|{user={\"setti... > 8 |#22:27...|[1] |false |1 |0 |VYqt3L...|true |0 > |true |2014-0...|2014-0...|{*={re...|{__typ...|{user={\"setti... > 9 |#22:27...|[1] |false |1 |0 |9DzjOy...|true |0 > |true |2014-0...|2014-0...|{Cmx5G...|{__typ...|{user={\"setti... > > ----+---------+------+-------+--------+---------+---------+---------+---------+---------+---------+---------+---------+---------+----------------- > > > 10 item(s) found. Query executed in 0.017 sec(s). > orientdb {pumpup}> drop index Post.updatedAt > > > > > Removing index... > Dropped index in 0.238000 sec(s). > > > > > Index removed successfully > orientdb {pumpup}> select from Post order by updatedAt desc limit 10 > > > > ----+---------+------+-------+--------+---------+---------+---------+---------+---------+---------+---------+---------+---------+----------------- > # |@RID |_allow|private|actionId|likeCount|objectId > |isOptimiz|commentCo|hashtagsP|createdAt|updatedAt|ACL |user > |dataCache > > ----+---------+------+-------+--------+---------+---------+---------+---------+---------+---------+---------+---------+---------+----------------- > 0 |#22:27...|[1] |false |1 |0 |CaWJkr...|true |0 > |true |2014-0...|2014-0...|{CMvKU...|{__typ...|{user={\"setti... > 1 |#22:27...|[1] |false |1 |0 |LFAaO2...|true |0 > |true |2014-0...|2014-0...|{*={re...|{__typ...|{user={\"setti... > 2 |#22:27...|[1] |false |1 |0 |CuTTUz...|true |0 > |true |2014-0...|2014-0...|{mcAlf...|{__typ...|{user={\"setti... > 3 |#22:27...|[1] |false |1 |0 |fdy9X2...|true |0 > |true |2014-0...|2014-0...|{*={re...|{__typ...|{user={\"setti... > 4 |#22:27...|[1] |false |1 |3 |fSyNBu...|true |0 > |true |2014-0...|2014-0...|{lM6Fp...|{__typ...|{user={\"setti... > 5 |#22:27...|[1] |false |1 |0 |h8ogaR...|true |0 > |true |2014-0...|2014-0...|{x3ikH...|{__typ...|{user={\"setti... > 6 |#22:27...|[1] |false |1 |0 |ew2bqg...|true |0 > |true |2014-0...|2014-0...|{YZHrd...|{__typ...|{user={\"setti... > 7 |#22:27...|[1] |false |1 |0 |crlcnr...|true |0 > |true |2014-0...|2014-0...|{HYVI5...|{__typ...|{user={\"setti... > 8 |#22:27...|[1] |false |1 |0 |VYqt3L...|true |0 > |true |2014-0...|2014-0...|{*={re...|{__typ...|{user={\"setti... > 9 |#22:27...|[1] |false |1 |0 |9DzjOy...|true |0 > |true |2014-0...|2014-0...|{Cmx5G...|{__typ...|{user={\"setti... > > ----+---------+------+-------+--------+---------+---------+---------+---------+---------+---------+---------+---------+---------+----------------- > > > 10 item(s) found. Query executed in 10.676 sec(s). > orientdb {pumpup}> explain select from Post order by updatedAt desc limit > 10 > > > Profiled command > '{fullySortedByIndex:true,documentReads:479025,current:#22:2719287,documentAnalyzedCompatibleClass:10,recordReads:479025,involvedIndexes:[1],fetchingFromTargetElapsed:4550,indexIsUsedInOrderBy:true,evaluated:10,elapsed:4551.123,resultType:collection,resultSize:10}' > in 4.558000 sec(s): > {"@type":"d","@version":0,"fullySortedByIndex":true,"documentReads":479025 > ,"current":"#22:2719287","documentAnalyzedCompatibleClass":10, > "recordReads":479025,"involvedIndexes":["PumpUp.updatedAt"], > "fetchingFromTargetElapsed":4550,"indexIsUsedInOrderBy":true,"evaluated": > 10,"elapsed":4551.123,"resultType":"collection","resultSize":10, > "@fieldTypes": > "documentReads=l,documentAnalyzedCompatibleClass=l,recordReads=l,involvedIndexes=e,fetchingFromTargetElapsed=l,evaluated=l,elapsed=f" > } > > > Here is the info (for clarity): > orientdb {pumpup}> info class Post > > > Class................: Post > Super class..........: V > Default cluster......: post (id=22) > Supported cluster ids: [22] > Cluster selection....: round-robin > > > PROPERTIES > > -------------------------------+-------------+-------------------------------+-----------+----------+----------+-----------+-----------+----------+ > NAME | TYPE | LINKED TYPE/CLASS > | MANDATORY | READONLY | NOT NULL | MIN | MAX | COLLATE | > > -------------------------------+-------------+-------------------------------+-----------+----------+----------+-----------+-----------+----------+ > workoutLog_pointerProcessed | BOOLEAN | null > | false | false | false | | | default | > weightLog_pointerProcessed | BOOLEAN | null > | false | false | false | | | default | > workout | EMBEDDEDMAP | STRING > | false | false | false | | | default | > activityLog | EMBEDDEDMAP | STRING > | false | false | false | | | default | > workoutLog | EMBEDDEDMAP | STRING > | false | false | false | | | default | > activityLog_pointerProcessed | BOOLEAN | null > | false | false | false | | | default | > workout_pointerProcessed | BOOLEAN | null > | false | false | false | | | default | > weightLog | EMBEDDEDMAP | STRING > | false | false | false | | | default | > user_pointerProcessed | BOOLEAN | null > | false | false | false | | | default | > user | EMBEDDEDMAP | STRING > | false | false | false | | | default | > replacedACL | BOOLEAN | null > | false | false | false | | | default | > createdAt | DATETIME | null > | false | false | false | | | default | > ACL | EMBEDDEDMAP | EMBEDDEDMAP > | false | false | false | | | default | > objectId | STRING | null > | false | false | false | | | default | > updatedAt | DATETIME | null > | false | false | false | | | default | > _allowDelete | LINKSET | OIdentity > | false | false | false | | | default | > _allowRead | LINKSET | OIdentity > | false | false | false | | | default | > _allow | LINKSET | OIdentity > | false | false | false | | | default | > _allowUpdate | LINKSET | OIdentity > | false | false | false | | | default | > > -------------------------------+-------------+-------------------------------+-----------+----------+----------+-----------+-----------+----------+ > > > INDEXES (5 altogether) > -------------------------------+----------------+ > NAME | PROPERTIES | > -------------------------------+----------------+ > Post.workout_pointerProcessed | workout_pointerProcessed| > Post.activityLog_pointerProcessed| activityLog_pointerProcessed| > Post.workoutLog_pointerProcessed| workoutLog_pointerProcessed| > Post.user_pointerProcessed | user_pointerProcessed| > Post.weightLog_pointerProcessed| weightLog_pointerProcessed| > -------------------------------+----------------+ > orientdb {pumpup}> info class pumpup > > > Class................: PumpUp > Super class..........: ORestricted > Default cluster......: null (id=-1) > Supported cluster ids: [-1] > Cluster selection....: round-robinBase classes.........: V, E > > > > > PROPERTIES > > -------------------------------+-------------+-------------------------------+-----------+----------+----------+-----------+-----------+----------+ > NAME | TYPE | LINKED TYPE/CLASS > | MANDATORY | READONLY | NOT NULL | MIN | MAX | COLLATE | > > -------------------------------+-------------+-------------------------------+-----------+----------+----------+-----------+-----------+----------+ > replacedACL | BOOLEAN | null > | false | false | false | | | default | > createdAt | DATETIME | null > | false | false | false | | | default | > ACL | EMBEDDEDMAP | EMBEDDEDMAP > | false | false | false | | | default | > objectId | STRING | null > | false | false | false | | | default | > updatedAt | DATETIME | null > | false | false | false | | | default | > _allowDelete | LINKSET | OIdentity > | false | false | false | | | default | > _allowRead | LINKSET | OIdentity > | false | false | false | | | default | > _allow | LINKSET | OIdentity > | false | false | false | | | default | > _allowUpdate | LINKSET | OIdentity > | false | false | false | | | default | > > -------------------------------+-------------+-------------------------------+-----------+----------+----------+-----------+-----------+----------+ > > > INDEXES (3 altogether) > -------------------------------+----------------+ > NAME | PROPERTIES | > -------------------------------+----------------+ > PumpUp.replacedACL | replacedACL | > PumpUp.objectId | objectId | > PumpUp.updatedAt | updatedAt | > -------------------------------+----------------+ > > > On Saturday, July 26, 2014 1:10:47 PM UTC-4, Lvc@ wrote: >> >> Hi Garrett, >> Indexes against base class should work, maybe this is a bug. Can you try >> to create the very same index against the Post class? >> >> Lvc@ >> >> >> >> On 25 July 2014 20:31, Garrett Gottlieb <[email protected]> wrote: >> >>> Would it be faster to separate my abstract PumpUp class and create the >>> indexes/properties separately for every class? Or perhaps it would be >>> faster if the abstract class maintained a separate index for every class? >>> >>> >>> On Friday, July 25, 2014 1:56:31 PM UTC-4, Garrett Gottlieb wrote: >>> >>>> Hi Luca, >>>> >>>> I've updated the index: >>>> >>>> CREATE INDEX PumpUp.updatedAt ON PumpUp (updatedAt) NOTUNIQUE >>>> >>>> >>>> orientdb {pumpup}> info class PumpUp >>>> >>>> >>>> Class................: PumpUp >>>> Super class..........: ORestricted >>>> Default cluster......: null (id=-1) >>>> Supported cluster ids: [-1] >>>> Cluster selection....: round-robinBase classes.........: E, V >>>> >>>> >>>> >>>> >>>> PROPERTIES >>>> -------------------------------+-------------+-------------- >>>> -----------------+-----------+----------+----------+-------- >>>> ---+-----------+----------+ >>>> NAME | TYPE | LINKED TYPE/CLASS >>>> | MANDATORY | READONLY | NOT NULL | MIN | MAX | >>>> COLLATE | >>>> -------------------------------+-------------+-------------- >>>> -----------------+-----------+----------+----------+-------- >>>> ---+-----------+----------+ >>>> replacedACL | BOOLEAN | null >>>> | false | false | false | | | >>>> default | >>>> createdAt | DATETIME | null >>>> | false | false | false | | | >>>> default | >>>> ACL | EMBEDDEDMAP | EMBEDDEDMAP >>>> | false | false | false | | | >>>> default | >>>> objectId | STRING | null >>>> | false | false | false | | | >>>> default | >>>> updatedAt | DATETIME | null >>>> | false | false | false | | | >>>> default | >>>> _allowDelete | LINKSET | OIdentity >>>> | false | false | false | | | >>>> default | >>>> _allowRead | LINKSET | OIdentity >>>> | false | false | false | | | >>>> default | >>>> _allow | LINKSET | OIdentity >>>> | false | false | false | | | >>>> default | >>>> _allowUpdate | LINKSET | OIdentity >>>> | false | false | false | | | >>>> default | >>>> -------------------------------+-------------+-------------- >>>> -----------------+-----------+----------+----------+-------- >>>> ---+-----------+----------+ >>>> >>>> >>>> INDEXES (3 altogether) >>>> -------------------------------+----------------+ >>>> NAME | PROPERTIES | >>>> -------------------------------+----------------+ >>>> PumpUp.replacedACL | replacedACL | >>>> PumpUp.objectId | objectId | >>>> PumpUp.updatedAt | updatedAt | >>>> -------------------------------+----------------+ >>>> >>>> >>>> But am still getting a very slow query: >>>> orientdb {pumpup}> select count(*) from post; select from post order by >>>> updatedAt desc limit 10 >>>> >>>> >>>> ----+------+------ >>>> # |@RID |count >>>> ----+------+------ >>>> 0 |#-1:-1|562449 >>>> ----+------+------ >>>> >>>> >>>> 1 item(s) found. Query executed in 0.005 sec(s). >>>> ----+---------+------+-------+--------+---------+---------+- >>>> --------+---------+---------+---------+---------+---------+- >>>> --------+---------+---------+---------+---------+---------+- >>>> --------+---------+---------+--------- >>>> # |@RID |_allow|private|actionId|likeCount|relatedId|objectId >>>> |isOptimiz|commentCo|note |hashtagsP|relatedId|showInPop >>>> |mentionsP|activityL|createdAt|updatedAt|ACL |user >>>> |workoutLo|activityL|details >>>> ----+---------+------+-------+--------+---------+---------+- >>>> --------+---------+---------+---------+---------+---------+- >>>> --------+---------+---------+---------+---------+---------+- >>>> --------+---------+---------+--------- >>>> 0 |#22:56...|[1] |false |1300 |0 >>>> |wCzAgA...|TcbNo7...|true |0 |null |true |true >>>> |false |null |true |2014-0...|2014-0...|{NKXBg...|{__typ...|null >>>> |{__typ...|null >>>> 1 |#22:56...|[1] |false |1300 |0 >>>> |qtD8fi...|DzDs8s...|true |0 |Just d...|true |true >>>> |false |null |true |2014-0...|2014-0...|{M5uAi...|{__typ...|null >>>> |{__typ...|null >>>> 2 |#22:56...|[1] |null |207 |0 >>>> |4xZ5m8...|XSPIL4...|true |0 |null |true |true >>>> |null |null |null >>>> |2013-0...|2014-0...|{*={re...|{__typ...|{__typ...|null >>>> |{"calo... >>>> 3 |#22:56...|[1] |null |207 |0 >>>> |SzYHuf...|rDI2gj...|true |0 |null |true |true >>>> |null |null |null >>>> |2013-0...|2014-0...|{*={re...|{__typ...|{__typ...|null >>>> |{"calo... >>>> 4 |#22:56...|[1] |null |207 |0 >>>> |fxDE6N...|Tq422o...|true |0 |Good |true |true >>>> |null |null |null >>>> |2013-0...|2014-0...|{*={re...|{__typ...|{__typ...|null >>>> |{"calo... >>>> 5 |#22:56...|[1] |null |207 |0 >>>> |bF0os7...|couPB8...|true |0 |null |true |true >>>> |null |null |null >>>> |2013-0...|2014-0...|{*={re...|{__typ...|{__typ...|null >>>> |{"calo... >>>> 6 |#22:56...|[1] |null |207 |0 >>>> |CAVFoT...|MVJRkX...|true |0 |null |true |true >>>> |null |null |null >>>> |2013-0...|2014-0...|{*={re...|{__typ...|{__typ...|null >>>> |{"calo... >>>> 7 |#22:56...|[1] |null |207 |0 >>>> |kJ1RTD...|sDrLhX...|true |0 |null |true |true >>>> |null |null |null >>>> |2013-0...|2014-0...|{d4CpX...|{__typ...|{__typ...|null >>>> |{"calo... >>>> 8 |#22:56...|[1] |false |1 |0 |null >>>> |Qqj1ru...|true |0 |null |true |null |null |true >>>> |null |2014-0...|2014-0...|{ldsbq...|{__typ...|null |null >>>> |null >>>> 9 |#22:56...|[1] |false |1 |0 |null >>>> |zVSfuW...|true |0 |null |true |null |null |true >>>> |null |2014-0...|2014-0...|{*={re...|{__typ...|null |null >>>> |null >>>> ----+---------+------+-------+--------+---------+---------+- >>>> --------+---------+---------+---------+---------+---------+- >>>> --------+---------+---------+---------+---------+---------+- >>>> --------+---------+---------+--------- >>>> >>>> >>>> 10<span style="color: #000;" class="st >>>> ... >>> >>> -- >>> >>> --- >>> 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]. > 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]. For more options, visit https://groups.google.com/d/optout.
