Dain Sundstrom wrote: > Most people are looking for limit and offset, so they can implement > result paging. Without support for limit and offset most people use > setMaxRows in the statement for limit and something like relative(int) > in the result set for the offset.
> The problem I see, is the planner > does not know how much the user intends to skip until after the query is > processed, but maybe this can't be optimized anyway. Yes, that kind of paging needs to count all the records from the beginning to the end of page, which is on average 50% of all the records in the database. That does not work well with higher isolation levels, especially without read-only transactions and multiversion architecture... What _can_ be optimized however is a different paging method, in which you don't use record numbers (which make sense only in context of whole resultset you're trying to page and will shift as records are added/deleted), but use ordering index key value to position your page in the result set. You select first N records that have ordering key >= page starting value, in the order of the ordering index, to get the page. This type of paging only provides navigation to next/previous/first/last page, but that is a good thing when the underlying result set is changing a lot anyway, and is even good for the concurrency (by locking just the page, not whole resultset or all skipped records from beginning). Query planner should recognize I'm trying to retrieve a limited number of records in specific index order starting with specific value, and return the correct records by planning record retrieval based on the ordering index, not on how the records happen to be stored (which would be some random records that fulfill the >= predicate as SapDB does, which is totally useless). Jan
signature.asc
Description: OpenPGP digital signature
