Reverse and incremental index sorting optimizations #4041

katzyn · 2024-04-14T04:48:39Z

Indexes can now be scanned in reverse order. With these changes both ascending and descending indexes on the same column aren't needed any more for index sorting optimization, only one index is enough. Closes An available index is never used for ordering, when the requested order is DESC #701. Some limitations still exist. For nullable columns ASC NULLS FIRST index can be used for ASC NULLS FIRST and DESC NULLS LAST, but it cannot be used for ASC NULLS LAST or DESC NULLS FIRST. A multi-column index can be used for the same multi-column ORDER BY only if all column are specified with the same order as in the index, or all columns are specified in reverse order. Due to (2) some indexes where not all columns have the same order can still be used.
Index that covers only first one or few columns from the ORDER BY clause now may still be used for sorting with an index. It is important for queries with the FETCH clause, because such queries can stop their execution earlier. We need to read additional rows only until a change in the matched indexed columns, all remaining rows aren't needed. This is similar to existing optimization for WITH TIES clause.
An old bug with ORDER BY _ROWID_ DESC is fixed, now it really returns rows in descending order.

katzyn added 2 commits April 14, 2024 12:12

Reverse and incremental index sorting optimizations

913692e

Fix building of documentation

9713b54

katzyn merged commit 0f29221 into h2database:master Apr 16, 2024

katzyn deleted the index branch April 16, 2024 00:42

vreuland mentioned this pull request Jul 16, 2024

[2.3.230] Regression in ORDER BY ... DESC on dates #4079

Closed

Provide feedback