3 ms·
> Instead of using OFFSET, the query becomes WHERE id > cursor ORDER BY id LIMIT 10 Wait. Surely "ORDER BY id OFFSET 20 LIMIT 10" works about the same as "WHER
by Joker_vD 1y ago
> Instead of using OFFSET, the query becomes WHERE id > cursor ORDER BY id LIMIT 10
Wait. Surely "ORDER BY id OFFSET 20 LIMIT 10" works about the same as "WHERE id > cursor ORDER BY id LIMIT 10", if "id" is indexed?
- asddubs 1y agono, because it has to count the amount of matching rows preceding the offset rows to determine the offset, i.e. iterate over all preceding rows. The cursor provides a starting point for the offset so in this instance it's not necessary. https://use-the-index-luke.com/sql/partial-results/fetch-next-page https://use-the-index-luke.com/sql/partial-results/fetch-nex...
- Joker_vD 1y agoIt only has to count if the index doesn't store the amounts of records in the "chunks" it manages, does it? I'm pretty sure B-trees do actually store the sizes of the subtrees.
- VGHN7XDuOXPAzol 1y agoAfaik Postgres doesn't. In my exposure it'd be quite uncommon for a b-tree to store the size of a subtree; would cause more churn/writes when updating trees. Perhaps some of the page-level Copy-on-Write databases (LMDB?) might do this, since they have to rewrite ancestor pages anyway.