DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version
	:content-type:content-transfer-encoding:content-disposition
	:references;
	b=aW3U2J4BA3SxCcuh9s5pFaowLEKQuohnlywkXUCvaibhj8Hj7zqw5+xZInLFJAEn5f
	TniOBJWwFuueMItFdRJ58hP7/FgUOsijtog6GWbpByUvDW/Rg0Q30Mx0jSWpWwWbPkbs
	X7hdPAsrGRzKlYNOB0G0Q8CxbQ4zArq8+8fBY=
Message-ID: <396486430809231507t46edf20fxab469bedbd78e8cc@mail.gmail.com>
Date: Tue, 23 Sep 2008 15:07:50 -0700
From: "Richard Broersma" <richard.broersma@gmail.com>
To: "Colin Copeland" <copelco@caktusgroup.com>
Subject: Re: query planner and scanning methods
Cc: pgsql-performance@postgresql.org,
	"team@caktusgroup.com s" <team@caktusgroup.com>
In-Reply-To: <8559E6C3-47ED-40E4-99E6-C56B322310DA@caktusgroup.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <8559E6C3-47ED-40E4-99E6-C56B322310DA@caktusgroup.com>

On Tue, Sep 23, 2008 at 2:22 PM, Colin Copeland <copelco@caktusgroup.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;


-- 
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug