From: Richard Broersma <richard.broersma@gmail.com>
To: Colin Copeland <copelco@caktusgroup.com>
Cc: pgsql-performance@postgresql.org, "team@caktusgroup.com s" <team@caktusgroup.com>
Subject: Re: query planner and scanning methods
Date: Tue, 23 Sep 2008 15:07:50 -0700
Message-ID: <396486430809231507t46edf20fxab469bedbd78e8cc@mail.gmail.com> (raw)
In-Reply-To: <8559E6C3-47ED-40E4-99E6-C56B322310DA@caktusgroup.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: richard.broersma@gmail.com, copelco@caktusgroup.com, team@caktusgroup.com
Subject: Re: query planner and scanning methods
In-Reply-To: <396486430809231507t46edf20fxab469bedbd78e8cc@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox