From: Tom Lane <tgl@sss.pgh.pa.us>
To: Stuart Brooks <stuartb@cat.co.za>
Cc: pgsql-sql@postgresql.org
Subject: Re: Constraint on multicolumn index
Date: Fri, 10 Nov 2006 10:07:44 -0500
Message-ID: <20924.1163171264@sss.pgh.pa.us> (raw)
In-Reply-To: <021e01c704b1$ed75eec0$cc22a8c0@STU95>
References: <021e01c704b1$ed75eec0$cc22a8c0@STU95>
"Stuart Brooks" <stuartb@cat.co.za> 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
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: tgl@sss.pgh.pa.us, stuartb@cat.co.za
Subject: Re: Constraint on multicolumn index
In-Reply-To: <20924.1163171264@sss.pgh.pa.us>
* 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