Combining SQL SELECT and MATCH kills performance

Skip to first unread message


Jul 21, 2016, 3:10:41 PM7/21/16
to OrientDB
1) Match works beautifully(once I got the hang of it). However as suggested in the otherwise excellent blog post, "Pattern matching with OrientDB",
    select something from (match .....) takes ages while the match itself (i.e. without select) returns in milliseconds. How come?

2) In the same example(above) select from (match   ,) the suggested return of aliases, as RETURN A as a, B as b;  do not work (2.2.4).

Finally - a tiny detail at towards the very end of in the same blog, "Remember always have to use RETURN $elements or RETURN $pathElements".

Thank you Luigi! That really saved my day (just like the day when I discovered . in vi, to repeat last command) Hurray!

Luigi Dell'Aquila

Jul 22, 2016, 2:41:29 AM7/22/16

I think the difference in performance is due to two factors:
- MATCH statement is a new addition in the engine, so it still has some sharp edges. You can expect important improvements in absolute performance and in integration with the rest of SQL in next releases
- if you are executing the query in Studio, the MATCH alone will return only 20 elements by default (that is typically very fast), while if you use it as a subquery for a select, it has to do the full execution and then pass the result to the upper level. This can be optimized of course and I'm working on it.

Two questions:

1. could you please post your query? maybe it can be rewritten with the MATCH only...
2. could you provide the example that fails with aliases in the RETURN? They are supposed to work fine, if it's not the case I'll fix it...




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
For more options, visit


Jul 22, 2016, 3:34:23 AM7/22/16
to OrientDB

thanks. I am a bit reluctant to share the query itself here at this time. However, your answer actually reminded me of another
issue. Although the match with 'where: ...'  works as expected, returning all data (very fast also when limit is 400+). 
Applying my 'arcsinh(arg)' function to columns in the return part did not work. Hence the attempt  with select, which works, but very very slow even when adding PARALLEL(which even in my 2.2.5 makes a big difference).

By the way, how can MATCH show data from two "branches" (or a fork) from a vertex with two edges? 
As you say, MATCH is probably not yet fully matured, so I am looking forward to upcoming versions. Having said this, I would like to add
that in the latest releases you made fantastic progress!   

Regarding the alias part in the RETURN section. From my point of view, the example I referred from you blog post is perhaps a bit
superfluous? Why return aliases when the select columns would need yet another set of aliases as well? 


Aug 2, 2016, 3:23:30 PM8/2/16
to OrientDB
This issue can now safely be closed. Thank you very much Luigi!


On Thursday, July 21, 2016 at 9:10:41 PM UTC+2, Tore wrote:
Reply all
Reply to author
0 new messages