herddb icon indicating copy to clipboard operation
herddb copied to clipboard

Range scans on Indexes doesn't take advantage of index order

Open diegosalvi opened this issue 7 years ago • 1 comments

Queries like SELECT * FROM mytable WHERE indexedfield >= v ORDER BY indexedfield LIMIT n ideally should scan just n values.

Currently every value after v is retrieved, filtered and sorted before limiting the output.

Knownledge of ordering by index should be push down in some way to data scanner with result limit knowledge too.

There should be some way to improve inverted index order scanning too (ex: index defined as ASC and ordering by DESC)

This issue is similar to #105 but on secondary indexes

diegosalvi avatar Apr 21 '17 13:04 diegosalvi

With the new streaming data scanner it is simpler to do this implementation, we have only to have a way to understand that the request order matches the collation of the index.

A second step would be to instruct the Planner to prefer a secondary index scan in case of sorted access

eolivelli avatar Dec 01 '17 14:12 eolivelli