Message-ID: <42BC5D5F.9000609@arbash-meinel.com>
Date: Fri, 24 Jun 2005 14:22:07 -0500
From: John A Meinel <john@arbash-meinel.com>
User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317)
MIME-Version: 1.0
To: Yves Vindevogel <yves.vindevogel@implements.be>,
	pgsql-performance@postgresql.org
Subject: Re: Speed with offset clause
References: <1c365a8bdeb09957fadda7a6fdc8ff02@implements.be>
In-Reply-To: <1c365a8bdeb09957fadda7a6fdc8ff02@implements.be>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="------------enigBF8C1D6E9A2CE84996D266BB"

This is an OpenPGP/MIME signed message (RFC 2440 and 3156)
--------------enigBF8C1D6E9A2CE84996D266BB
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: quoted-printable

Yves Vindevogel wrote:

> Hi again all,
>
> My queries are now optimised. They all use the indexes like they should=
=2E
> However, there's still a slight problem when I issue the "offset" claus=
e.
>
> 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 =3D page 24000 - 1 =3D=
=20
> 23999, I issue the offset of 23999 * 25
> This take a long time to run, about 5-10 seconds whereas offset below=20
> 100 take less than a second.
>
> Can I speed this up ?
>
>
> Met vriendelijke groeten,
> Bien =E0 vous,
> Kind regards,
>
> *Yves Vindevogel*
> *Implements*
>
Postgres has the optimization that it will plan a query, and once it=20
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=20
first, before it can apply the limit.
If you can, (as mentioned in the other post), try to refine your index=20
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=20
the amount might be changing).

A potentially better thing, is if you have an index you are using, you=20
could use a subselect so that the only portion that needs to have 60k=20
rows is a single column.

Maybe an example:
Instead of saying:

SELECT * FROM table1, table2 WHERE table1.id =3D table2.id ORDER BY=20
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 =3D table1.id
    , table2
    WHERE table1.id =3D table2.id;

That means that the culling process is done on only a few rows of one=20
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=20
has a big influence on how well this will work.

John
=3D:->


--------------enigBF8C1D6E9A2CE84996D266BB
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.0 (Cygwin)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFCvF1fJdeBCYSNAAMRAg92AKCHqaqj/Ky96WI6VRekeeyZxnsS/QCgws5R
srECUpUSdVdVg2rfwU425rA=
=yAlJ
-----END PGP SIGNATURE-----

--------------enigBF8C1D6E9A2CE84996D266BB--