[OAK-2056] Optimize orderings by date fields

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.0.4, 1.0.8
• Fix Version/s: 1.1.4
• Component/s: query
• Labels: performance
• Assignee: Davide Giannella
• Reporter: Przemyslaw Pakulski
• Created: 27/Aug/14 09:34
• Updated: 30/Jan/15 12:30
• Resolved: 17/Dec/14 14:54

Description

Sorting by date fields is very slow in oak, especially if result set size is large.

I'm running the following JCR-SQL2 query

SELECT * FROM [cq:PageContent] AS [c] WHERE ISDESCENDANTNODE('/content')

which returns 3270 results on my oak repo.

Query execution times are as below
---------------------------------------
No order clause 		|  0,147 sec
ORDER BY [jcr:title]	        |  1,203 sec
ORDER BY [jcr:createdBy]	|  1,018 sec
ORDER BY [jcr:created]		| 25,229 sec

Ordering by date field adds extra 24 seconds overhead.