Sorting a result set of expand()

[recremd]

I've received the following question from our developers about a performance issue with sorting by an index. Here are the details.

We have a problem by sorting a result set of "expand". It is not using the index of the original class and we don’t know how to solve this problem.

The User vertex has a FriendsWith edge between users. The User vertex also has a "HasPost" edge between the Post vertex.

Post table has a NOTUNIQUE INDEX on Post.CreatedAt field, but the index is not used on querying the latest post of friends (and the response time is very high).

The query is: SELECT expand(both('FriendsWith').out('HasPost')) FROM #[USER_RID] ORDER BY CreatedAt DESC LIMIT 50

This is the result of the "explain" function:
{
"result": [
{
"@type": "d",
"@Version": 0,
"documentReads": 1,
"current": "#18:0",
"recordReads": 1,
"fetchingFromTargetElapsed": 0,
"expandElapsed": 878,
"orderByElapsed": 8138,
"evaluated": 1,
"elapsed": 9019.312,
"resultType": "collection",
"resultSize": 50,
"@fieldTypes": "documentReads=l,current=x,recordReads=l,fetchingFromTargetElapsed=l,expandElapsed=l,orderByElapsed=l,evaluated=l,elapsed=f"
}
],
"notification": "Query executed in 9.027 sec. Returned 1 record(s)"
}

The query: SELECT expand(both('FriendsWith').out('HasPost')) FROM #[USER_RID] DESC LIMIT 50 executes very fast without the ordering.

We would like to find the solution to sort the result set using the index of original table.

The OrientDB version we're running is 2.1.11.

Thank you.

[luigidellaquila]

Hi @recremd

I'm afraid it's not possible to use the index to do the sorting in this case. The expand returns a flat result-set that is the result of a traversal, and the traversal cannot rely on an index.

You can try to rewrite the query like follows:

SELECT FROM Post 
WHERE in('HasPost').both('FriendsWith') contains #[USER_RID] 
ORDER BY CreatedAt DESC LIMIT 50

This will use the index for sorting, but it will be much less efficient in general because it will have to traverse a lot of relationships, so in the end I'm afraid you will have even worse performance.

Thanks

Luigi