Skip site navigation (1) Skip section navigation (2)

Re: query planner and scanning methods

From: Colin Copeland <copelco(at)caktusgroup(dot)com>
To: "Richard Broersma" <richard(dot)broersma(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org, "team(at)caktusgroup(dot)com s" <team(at)caktusgroup(dot)com>
Subject: Re: query planner and scanning methods
Date: 2008-09-23 22:25:17
Message-ID: 6D0802D6-371A-470C-AB1C-3CC20E543061@caktusgroup.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Sep 23, 2008, at 6:07 PM, Richard Broersma wrote:

> On Tue, Sep 23, 2008 at 2:22 PM, Colin Copeland <copelco(at)caktusgroup(dot)com 
> > 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


In response to

Responses

pgsql-performance by date

Next:From: Richard BroersmaDate: 2008-09-23 22:57:31
Subject: Re: query planner and scanning methods
Previous:From: Richard BroersmaDate: 2008-09-23 22:07:50
Subject: Re: query planner and scanning methods

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group