CODEWITHSUNDEEP
performancesortingarangodb
Felix Berth
Felix Berth

Reputation: 131

ArangoDB 2.8 - sort by subquery result - failure

After move to 2.8 this simple query now froze server with 100% CPU usage ~10sec. In 2.7 (~30ms)

FOR P In Person
   LET EventLast = (
    FOR E In Event FILTER E.owner == P._id SORT E.date desc LIMIT 1 RETURN E.date
   )
SORT EventLast[0]
LIMIT 40
RETURN { _id: P._id, name:P.name }

Collection Event have skiplist index in date and hash index on owner

Without "SORT E.date desc" or "SORT EventLast[0]" - 1ms

Upvotes: 2

Views: 88

Answers (1)

stj
stj

Reputation: 9097

The query optimizer in 2.8-beta picked the skiplist index on date for the inner subquery. This index allows removing the SORT clause, but the inner query still needs to scan the entire index in reverse order until the first filter match. It does that as many times as there are documents in Person.

The 2.7 optimizer instead picked the hash index on owner and used a post-index-SORT. This was probably better in this case if the number of matches per index lookup is very small, but will be bad if the filter is very unselective.

The 2.8 optimizer will now again prefer the potentially more selective hash index for the inner query. A fix for this has been made today in the 2.8 branch, which will turn into a beta3 or rc (note that there will be a beta2 soon that won't yet contain the fix).

Upvotes: 4

Related Questions