X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id EC7B3D1B900 for ; Wed, 28 Jul 2004 00:30:46 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 05492-08 for ; Wed, 28 Jul 2004 03:30:42 +0000 (GMT) Received: from www.postgresql.com (www.postgresql.com [200.46.204.209]) by svr1.postgresql.org (Postfix) with ESMTP id 50E90D1CC1F for ; Wed, 28 Jul 2004 00:22:24 -0300 (ADT) Received: from mail.logi-track.com (burro.logi-track.com [213.239.193.212]) by www.postgresql.com (Postfix) with ESMTP id 5966ACF6401 for ; Tue, 27 Jul 2004 11:13:53 -0300 (ADT) Received: from kingfisher.intern.logi-track.com (G6110.g.pppool.de [80.185.97.16]) by mail.logi-track.com (Postfix) with ESMTP id 70AC930197; Tue, 27 Jul 2004 14:19:41 +0000 (UTC) Received: from kingfisher.intern.logi-track.com (localhost [127.0.0.1]) by kingfisher.intern.logi-track.com (Postfix) with SMTP id 4A479AB2D0; Tue, 27 Jul 2004 16:13:26 +0200 (CEST) Date: Tue, 27 Jul 2004 16:13:25 +0200 From: Markus Schaber To: "Merlin Moncure" Cc: Subject: Re: best way to fetch next/prev record based on index Message-Id: <20040727161325.213b11eb@kingfisher.intern.logi-track.com> In-Reply-To: <6EE64EF3AB31D5448D0007DD34EEB34101AEF5@Herge.rcsinc.local> References: <6EE64EF3AB31D5448D0007DD34EEB34101AEF5@Herge.rcsinc.local> Organization: logi-track ag, =?ISO-8859-15?Q?z=FCrich?= X-Mailer: Sylpheed-Claws 0.9.12 (GTK+ 1.2.10; i386-pc-linux-gnu) X-Face: Nx5T&>Nj$VrVPv}sC3IL&)TqHHOKCz/|)R$i"*r@w0{*I6w; UNU_hdl1J4NI_m{IMztq=>cmM}1gCLbAF+9\#CGkG8}Y{x%SuQ>1#t:; Z(|\qdd[i]HStki~#w1$TPF}:0w-7"S\Ev|_a$K wrote: > So, for a table t with a three part key over columns a,b,c, the query > to read the next value from t for given values a1, b1, c1 is >=20 > select * from t where > a >=3D a1 and > (a > a1 or b >=3D b1) and > (a > a1 or b > b1 or c > c1) You mut not rely on such trickery to get any ordering, as the SQL data model contains no ordering, and a query optimizer is free to deliver you the tuples in any order it feels like. Why don't you add a 'ORDER BY a,b,c ASC' to your query? > Interestingly, it is possible to rewrite the above query by switching > and with or and >=3D with >. However when written that way, the planner > almost never gets it right. That's the reason why you cannot rely on any implicit ordering, the planner is free to rewrite a query as it likes as long as it delivers the same tuples, but in any order it wants. > My problem is deceptively simple: how you read the next record from a > table based on a given set of values? In practice, this is difficult > to implement. If anybody can suggest a alternative/better way to > this, I'm all ears. So you really want something like 'SELECT * FROM t WHERE a>=3Da1 AND b>=3Db1 AND c>=3Dc1 ORDER BY a,b,c ASC L= IMIT 1' HTH, Markus --=20 markus schaber | dipl. informatiker logi-track ag | rennweg 14-16 | ch 8001 z=FCrich phone +41-43-888 62 52 | fax +41-43-888 62 53 mailto:schabios@logi-track.com | www.logi-track.com