[OAK-8734] Sorted query with multiple path restrictions and sorting performs very slowly if one of the passed paths is a traversal

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.20.0
• Assignee: Nitin Gupta
• Reporter: Nitin Gupta
• Created: 01/Nov/19 07:55
• Updated: 16/Dec/22 01:54
• Resolved: 14/Nov/19 07:40

Description

A query like [1] with multiple path restrictions and an order can be extremely slow .

• Removing the orderby makes it much faste.
• Removing one path will make it faster.

The issue appears to be that, as soon as one path-restricred subquery is not handled at the index (ie its a traversal, since the cost of traversal on an empty path is 0), it appears that the entire sorting happens in the query engine which makes the query very very slow if there are lots of items (and defeats optimisations like guessTotal which prevent iteration / inflation of the complete result set).
[1]

(
  /jcr:root/content/dam/products//element(*, dam:Asset)
|
  /jcr:root/content/dam/projects//element(*, dam:Asset))
  order by jcr:content/@jcr:lastModified descendin
)

Issue Links

• links to: GitHub Pull Request #160