We’ve been experimenting with the new improvements in 5.0 regarding pagination pushdown to the indexes and index order. However, we’ve discovered a difficulty using this when bidirectional sorting in the UI is required. For example, a grid with a column header that the user can click to toggle sort direction.
The following examples use the beer-sample bucket. First, we create this index to support ascending sorts:
CREATE INDEX `beerName` ON `beer-sample` (`name`) WHERE `type` = 'beer'
At this point, we can see that this query uses pagination push down to the index, and IndexScan2 produces only 10 rows:
SELECT * FROM `beer-sample`
WHERE `type` = 'beer'
AND `name` IS NOT MISSING
ORDER BY `name`
OFFSET 10 LIMIT 10
However, as expected, if we reverse the sort direction to descending the pagination isn’t pushed down and IndexScan2 produces 5892 rows.
SELECT * FROM `beer-sample`
WHERE `type` = 'beer'
AND `name` IS NOT MISSING
ORDER BY `name` DESC
OFFSET 10 LIMIT 10
To address this, we create a second index with the opposite direction:
CREATE INDEX `beerNameDesc` ON `beer-sample` (`name` DESC) WHERE `type` = 'beer'
However, at this point things don’t work the way you expect. The index selection algorithm is not taking into account the directionality of the index. This means that for any given query (either ASC or DESC), it might select either one of the two indexes. This means that the IndexScan2 step will randomly return 10 rows and run efficiently, or will return 5892 rows and require lots more fetches and an additional sort operation on the query node. For a large dataset, this could make a drastic difference in query performance.
We’ve been able to work around this limitation for now by using the USE INDEX clause to select the specific index required based on the sort direction. However, this is non-ideal as it makes the environment somewhat more fragile during ongoing development.
I looked in JIRA and didn’t notice an issue on this topic, so I just wanted to make sure it was on the radar. Or if there’s another way to approach this, please let me know.
Thanks,
Brant