Re: How to improve traversal query performance
Manabu Kotani <smallcany...@...>
Hi Marc,
toggle quoted messageShow quoted text
Profile outputs I tried. 1. g.V().has('serial',within('XXXXXX','YYYYYY')).inE('assembled').outV() ---------------------------------------------------------------------- gremlin> g.V().has('serial', within('1654145144','1648418968','1652445288','1654952168','1653379120','1654325440','1653383216','1658298568','1649680536','1649819672','1654964456','1649729552','1656103144','1655460032','1656111336','1654669360')).inE('assembled').outV().profile() ==>Traversal Metrics Step Count Traversers Time (ms) % Dur ============================================================================================================= JanusGraphStep([],[serial.within([1654145144, 1... 16 16 18.860 63.13 \_condition=((serial = 1654145144 OR serial = 1648418968 OR serial = 1652445288 OR serial = 1654952168 OR serial = 1653379120 OR serial = 1654325440 OR serial = 1653383216 OR serial = 1658298568 OR se rial = 1649680536 OR serial = 1649819672 OR serial = 1654964456 OR serial = 1649729552 OR seri al = 1656103144 OR serial = 1655460032 OR serial = 1656111336 OR serial = 1654669360)) \_orders=[] \_isFitted=true \_isOrdered=true \_query=multiKSQ[16]@2000 \_index=bySerial optimization 0.058 optimization 0.694 backend-query 16 17.823 \_query=bySerial:multiKSQ[16]@2000 \_limit=2000 JanusGraphVertexStep(IN,[assembled],vertex) 73 73 11.016 36.87 \_condition=type[assembled] \_orders=[] \_isFitted=true \_isOrdered=true \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d \_multi=true \_vertices=16 optimization 0.205 backend-query 73 9.332 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d >TOTAL - - 29.877 - 2. g.V().has('serial',within('XXXXXX','YYYYYY')).as('a').in('assembled').inE('assembled').where(outV(), neq('a')).outV() // query not tested ---------------------------------------------------------------------- gremlin> g.V().has('serial', within('1654145144','1648418968','1652445288','1654952168','1653379120','1654325440','1653383216','1658298568','1649680536','1649819672','1654964456','1649729552','1656103144','1655460032','1656111336','1654669360')).as('a').in('assembled').inE('assembled').where(outV().is(neq('a'))).outV().profile() ==>Traversal Metrics Step Count Traversers Time (ms) % Dur ============================================================================================================= JanusGraphStep([],[serial.within([1654145144, 1... 16 16 19.980 26.52 \_condition=((serial = 1654145144 OR serial = 1648418968 OR serial = 1652445288 OR serial = 1654952168 OR serial = 1653379120 OR serial = 1654325440 OR serial = 1653383216 OR serial = 1658298568 OR se rial = 1649680536 OR serial = 1649819672 OR serial = 1654964456 OR serial = 1649729552 OR seri al = 1656103144 OR serial = 1655460032 OR serial = 1656111336 OR serial = 1654669360)) \_orders=[] \_isFitted=true \_isOrdered=true \_query=multiKSQ[16]@2000 \_index=bySerial optimization 0.026 optimization 0.588 backend-query 16 18.813 \_query=bySerial:multiKSQ[16]@2000 \_limit=2000 JanusGraphVertexStep(IN,[assembled],vertex) 73 73 6.521 8.66 \_condition=type[assembled] \_orders=[] \_isFitted=true \_isOrdered=true \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d \_multi=true \_vertices=16 optimization 0.154 backend-query 73 5.310 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d JanusGraphVertexStep(IN,[assembled],edge) 2578 2578 20.170 26.77 \_condition=type[assembled] \_orders=[] \_isFitted=true \_isOrdered=true \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d \_multi=true \_vertices=59 optimization 0.032 backend-query 2578 10.266 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d TraversalFilterStep([EdgeVertexStep(OUT), Profi... 2578 2578 21.824 28.97 EdgeVertexStep(OUT) 2578 2578 4.776 IsStep(neq(a)) 6.172 EdgeVertexStep(OUT) 2578 2578 6.842 9.08 >TOTAL - - 75.338 - 3. Results I want to get. ---------------------------------------------------------------------- g.V().has('serial', within('1654145144','1648418968','1652445288','1654952168','1653379120','1654325440','1653383216','1658298568','1649680536','1649819672','1654964456','1649729552','1656103144','1655460032','1656111336','1654669360')).as('a').repeat(inE('assembled').as('b').outV().as('c').simplePath()).emit().select('a').id().as('parent').select('b').values('work_date').as('work_date').select('c').values('serial').as('child').select('parent','child','work_date').order().by('parent').by('child').by('work_date').profile() ==>Traversal Metrics Step Count Traversers Time (ms) % Dur ============================================================================================================= JanusGraphStep([],[serial.within([1654145144, 1... 16 16 24.028 4.95 \_condition=((serial = 1654145144 OR serial = 1648418968 OR serial = 1652445288 OR serial = 1654952168 OR serial = 1653379120 OR serial = 1654325440 OR serial = 1653383216 OR serial = 1658298568 OR se rial = 1649680536 OR serial = 1649819672 OR serial = 1654964456 OR serial = 1649729552 OR seri al = 1656103144 OR serial = 1655460032 OR serial = 1656111336 OR serial = 1654669360)) \_orders=[] \_isFitted=true \_isOrdered=true \_query=multiKSQ[16]@2000 \_index=bySerial optimization 0.074 optimization 1.256 backend-query 16 312.583 \_query=bySerial:multiKSQ[16]@2000 \_limit=2000 RepeatStep([JanusGraphVertexStep(IN,[assembled]... 2925 2925 272.924 56.26 JanusGraphVertexStep(IN,[assembled],edge)@[b] 2925 2925 223.728 \_condition=type[assembled] \_orders=[] \_isFitted=true \_isOrdered=true \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d \_multi=true \_vertices=9 optimization 0.203 backend-query 5 1.557 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.020 backend-query 161 2.356 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.125 backend-query 9 25.853 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.023 backend-query 8 2.168 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.024 backend-query 0 1.808 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.026 backend-query 5 1.354 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.024 backend-query 161 1.989 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.040 backend-query 9 3.490 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.026 backend-query 0 2.231 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.061 backend-query 5 1.877 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.027 backend-query 161 4.645 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.056 backend-query 10 2.554 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.073 backend-query 9 4.274 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.026 backend-query 0 1.199 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.026 backend-query 5 1.165 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.025 backend-query 161 8.010 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.032 backend-query 9 1.542 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.032 backend-query 4 5.402 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.038 backend-query 5 4.173 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.029 backend-query 161 4.113 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.056 backend-query 9 1.617 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.035 backend-query 0 1.517 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.044 backend-query 5 1.522 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.045 backend-query 161 1.985 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.029 backend-query 9 1.435 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.028 backend-query 0 1.034 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.021 backend-query 3 1.108 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.024 backend-query 161 1.785 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.036 backend-query 9 7.190 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.032 backend-query 8 12.321 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.044 backend-query 0 1.926 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.032 backend-query 5 1.782 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.028 backend-query 161 3.398 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.031 backend-query 9 1.412 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.026 backend-query 0 1.212 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.035 backend-query 5 1.283 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.028 backend-query 161 2.149 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.035 backend-query 9 1.415 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.025 backend-query 1 1.214 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.025 backend-query 3 1.313 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.027 backend-query 161 2.004 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.038 backend-query 9 8.265 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.031 backend-query 0 1.718 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.028 backend-query 5 1.489 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.029 backend-query 161 2.066 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.029 backend-query 9 1.361 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.024 backend-query 2 1.454 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.026 backend-query 5 1.234 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.025 backend-query 161 1.819 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.027 backend-query 9 1.361 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.026 backend-query 0 1.136 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.028 backend-query 5 1.265 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.029 backend-query 161 10.425 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.040 backend-query 9 2.437 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.050 backend-query 0 1.462 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.049 backend-query 5 2.208 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.028 backend-query 163 2.415 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.035 backend-query 9 1.252 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.027 backend-query 0 1.164 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.032 backend-query 4 1.335 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.028 backend-query 161 1.944 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.028 backend-query 9 1.473 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.026 backend-query 0 1.114 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.025 backend-query 3 1.279 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.025 backend-query 161 1.867 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.033 backend-query 9 1.463 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d optimization 0.027 backend-query 0 1.169 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@812bd43d EdgeVertexStep(OUT)@[c] 2925 2925 7.733 PathFilterStep(simple) 2925 2925 10.508 JanusGraphMultiQueryStep(RepeatEndStep) 2925 2925 14.827 RepeatEndStep 2925 2925 9.754 SelectOneStep(last,a) 2925 2925 8.340 1.72 IdStep@[parent] 2925 2925 7.347 1.51 SelectOneStep(last,b) 2925 2925 8.690 1.79 JanusGraphPropertiesStep([work_date],value)@[wo... 2925 2925 35.051 7.22 SelectOneStep(last,c) 2925 2925 9.512 1.96 JanusGraphPropertiesStep([serial],value)@[child] 2925 2925 79.337 16.35 \_condition=type[serial] \_orders=[] \_isFitted=true \_isOrdered=true \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@811c505d \_multi=true \_vertices=302 optimization 0.044 backend-query 302 53.962 \_query=org.janusgraph.diskstorage.keycolumnvalue.SliceQuery@811c505d SelectStep(last,[parent, child, work_date]) 2925 2925 10.705 2.21 OrderGlobalStep([[value(parent), asc], [value(c... 2925 2925 29.210 6.02 >TOTAL - - 485.149 - Best regards, Manabu 2020年11月26日木曜日 16:07:27 UTC+9 HadoopMarc:
|
|