On Sep 23, 2008, at 6:07 PM, Richard Broersma wrote:
On Tue, Sep 23, 2008 at 2:22 PM, Colin Copeland <copelco@xxxxxxxxxxxxxxx
> wrote:
dimension=# EXPLAIN ANALYZE
SELECT DISTINCT ON ("dimension_book"."call")
"dimension_book"."title"
FROM "dimension_book"
INNER JOIN "dimension_library_books"
ON ("dimension_book"."id" =
"dimension_library_books"."book_id")
WHERE ("dimension_book"."call" >= 'PA0000'
AND "dimension_library_books"."library_id" IN (12,15,20))
ORDER BY "dimension_book"."call" ASC
LIMIT 10 OFFSET 100;
Ya offset works by scanning over the first 100 rows. When the offsets
get big, it become a performance looser.
You can guarantee a faster index scan if you recall the last 10th
value from the previous query. Then remove the offset predicate and
replace it with the following WHERE clause:
WHERE ...
AND dimension_book.call > _last_queried_10th_row-dimension_book_call,
...
LIMIT 10;
Richard,
Yes, I was thinking about this too. How would one generate a list of
pages from this, though? I can't predict values of dimension_book.call
(it's not a serial number).
Thanks,
colin
--
Colin Copeland
Caktus Consulting Group, LLC
P.O. Box 1454
Carrboro, NC 27510
(919) 951-0052
http://www.caktusgroup.com
