From: John A Meinel <john@arbash-meinel.com>
To: Yves Vindevogel <yves.vindevogel@implements.be>
To: pgsql-performance@postgresql.org
Subject: Re: Speed with offset clause
Date: Fri, 24 Jun 2005 14:22:07 -0500
Message-ID: <42BC5D5F.9000609@arbash-meinel.com> (raw)
In-Reply-To: <1c365a8bdeb09957fadda7a6fdc8ff02@implements.be>
References: <1c365a8bdeb09957fadda7a6fdc8ff02@implements.be>
Yves Vindevogel wrote:
> Hi again all,
>
> My queries are now optimised. They all use the indexes like they should.
> However, there's still a slight problem when I issue the "offset" clause.
>
> We have a table that contains 600.000 records
> We display them by 25 in the webpage.
> So, when I want the last page, which is: 600k / 25 = page 24000 - 1 =
> 23999, I issue the offset of 23999 * 25
> This take a long time to run, about 5-10 seconds whereas offset below
> 100 take less than a second.
>
> Can I speed this up ?
>
>
> Met vriendelijke groeten,
> Bien à vous,
> Kind regards,
>
> *Yves Vindevogel*
> *Implements*
>
Postgres has the optimization that it will plan a query, and once it
reaches the limit, it can stop even though there is more data available.
The problem you are having is that it has to go through "offset" rows
first, before it can apply the limit.
If you can, (as mentioned in the other post), try to refine your index
so that you can reverse it for the second half of the data.
This is probably tricky, as you may not know how many rows you have (or
the amount might be changing).
A potentially better thing, is if you have an index you are using, you
could use a subselect so that the only portion that needs to have 60k
rows is a single column.
Maybe an example:
Instead of saying:
SELECT * FROM table1, table2 WHERE table1.id = table2.id ORDER BY
table1.date OFFSET x LIMIT 25;
You could do:
SELECT * FROM
(SELECT id FROM table1 OFFSET x LIMIT 25) as subselect
JOIN table1 ON subselect.id = table1.id
, table2
WHERE table1.id = table2.id;
That means that the culling process is done on only a few rows of one
table, and the rest of the real merging work is done on only a few rows.
It really depends on you query, though, as what rows you are sorting on
has a big influence on how well this will work.
John
=:->
Attachments:
signature.asc (application/pgp-signature, 252B) download
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: john@arbash-meinel.com, yves.vindevogel@implements.be
Subject: Re: Speed with offset clause
In-Reply-To: <42BC5D5F.9000609@arbash-meinel.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