Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 97C6E9FB302 for ; Fri, 10 Nov 2006 11:08:06 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 13483-02-5 for ; Fri, 10 Nov 2006 11:07:55 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 4E6549FB2F8 for ; Fri, 10 Nov 2006 11:07:54 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id kAAF7i9D020925; Fri, 10 Nov 2006 10:07:45 -0500 (EST) To: "Stuart Brooks" cc: pgsql-sql@postgresql.org Subject: Re: Constraint on multicolumn index In-reply-to: <021e01c704b1$ed75eec0$cc22a8c0@STU95> References: <021e01c704b1$ed75eec0$cc22a8c0@STU95> Comments: In-reply-to "Stuart Brooks" message dated "Fri, 10 Nov 2006 12:21:02 +0200" Date: Fri, 10 Nov 2006 10:07:44 -0500 Message-ID: <20924.1163171264@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200611/110 X-Sequence-Number: 26762 "Stuart Brooks" writes: > But if I want the next item following t=(a=10,b=100,c=1000): > select * from T > where (a=10 AND b=100 AND c>1000) OR (a=10 AND b>100) OR (a>10) > order by a,b,c; The correct way to handle this is to use a SQL-spec row comparison: where (a,b,c) > (10,100,1000) Unfortunately, that syntax does not work per-spec in any existing Postgres release. It will work properly (and use the index) in PG 8.2, for what that's worth. Not sure if there's any reasonable workaround in PG <= 8.1. You might want to check this old thread: http://archives.postgresql.org/pgsql-performance/2004-07/msg00188.php (note that none of the first few responses got the point :-() Also http://archives.postgresql.org/pgsql-hackers/2006-02/msg00209.php regards, tom lane