From: Ragnar Hafstað <gnari@simnet.is>
To: Alain <alainm@pobox.com>
Cc: Sql-Postgre <pgsql-sql@postgresql.org>
Cc: Diogenes <di@sene.info>
Subject: Re: Changed to: how to solve the get next 100 records problem
Date: Wed, 18 May 2005 17:53:52 +0000
Message-ID: <1116438832.8187.15.camel@localhost.localdomain> (raw)
In-Reply-To: <428B6263.5050402@pobox.com>
References: <DCD4903594CCF34CAFE200BDF3448D8D5B291A@tshuscodenmbx01.ERF.THOMSON.COM>
<20050517170636.GC12405@phlogiston.dyndns.org>
<428A2E81.2060501@pobox.com>
<1116353539.26520.5.camel@localhost.localdomain>
<428A3B54.4070902@pobox.com>
<1116367382.9362.11.camel@localhost.localdomain>
<428AA569.3010708@pobox.com>
<1116404906.9362.19.camel@localhost.localdomain>
<428B6263.5050402@pobox.com>
On Wed, 2005-05-18 at 12:42 -0300, Alain wrote:
> I found something that is both fast and simple (program side):
> ...
> subsequent selects are
> (select ... from tab WHERE skey=skey_last AND pkey>pkey_last
> ORDER BY skey,pkey LIMIT 100)
> UNION
> (select ... from tab WHERE skey>skey_last
> ORDER BY skey,pkey LIMIT 100)
> ORDER BY skey,pkey LIMIT 100;
> ...
> The only strange thing is that without the 3rd order by, the order is
> wrong. I didn't expect it because each select is created ordered. Is it
> expected that UNION mixes it all up? (using postgre 7.4.1)
ORDER BY on subselects are not garanteed by SQL to have any effect,
but Postgres tends to do what you want when possible and not
detrimental to performance.
In this case, Postgres would probably have kept the order had
you used UNION ALL
a plain UNION implies DISTINCT, which Postgres is free to
implement any way it wants, possibly destroying the order
in this case a UNION ALL is appropriate, as you know that
the 2 selects do not overlap. possibly, a future version
of the planner will be able to detect this.
in any case, the last ORDER BY LIMIT does not cost much, and it
protects you against implementation changes, and limits
the result to 100 records, which might be what you want.
> Please comment on this. I tested and it worked but I really new to sql
> and I feel insecure...
it's good.
gnari
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: gnari@simnet.is, alainm@pobox.com, di@sene.info
Subject: Re: Changed to: how to solve the get next 100 records problem
In-Reply-To: <1116438832.8187.15.camel@localhost.localdomain>
* 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