From: Greg Stark <gsstark@mit.edu>
To: Tomasz Myrta <jasiek@klaster.net>
Cc: Andras Kutrovics <n-drew@freemail.hu>
Cc: pgsql-sql@postgresql.org
Subject: Re: Simple SQL Question
Date: 06 Nov 2004 03:17:43 -0500
Message-ID: <87zn1vl7bs.fsf@stark.xeocode.com> (raw)
In-Reply-To: <418BC1F3.7080205@klaster.net>
References: <418BB051.6070808@freemail.hu>
<418BC1F3.7080205@klaster.net>
Tomasz Myrta <jasiek@klaster.net> writes:
> > select * from table1 LIMIT x
> > gives me the first x row of the result.
> > After that, I save the last value, and next time, I adjust
> > the query as
> > select * from table1 where itemkey>:lastvalue LIMIT x
>
> Why do you complicate it so much? Everything you need is:
>
> select * from table1 LIMIT x
> select * from table1 LIMIT x OFFSET x
> select * from table1 LIMIT x OFFSET 2*x
>
> Remember to sort rows before using limit/offset.
There are two good reasons to prefer his Andras' solution to yours.
a) If the data is modified between the two queries his will continue from
where the previous page left off. Yours will either skip records or overlap
with the previous page.
b) If itemkey is indexed his will be an efficient index scan that performs
similarly regardless of what page is being fetched. Yours will perform more
and more slowly as the user gets deeper into the results.
Note that both queries are wrong however. You need an "ORDER BY itemkey" or
else nothing guarantees the second page has any relation at all to the first
page.
--
greg
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-sql@postgresql.org
Cc: gsstark@mit.edu, jasiek@klaster.net, n-drew@freemail.hu
Subject: Re: Simple SQL Question
In-Reply-To: <87zn1vl7bs.fsf@stark.xeocode.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