Message-ID: <021e01c704b1$ed75eec0$cc22a8c0@STU95>
From: "Stuart Brooks" <stuartb@cat.co.za>
To: <pgsql-sql@postgresql.org>
Subject: Constraint on multicolumn index
Date: Fri, 10 Nov 2006 12:21:02 +0200
Organization: Intervid Technologies
MIME-Version: 1.0
Content-Type: text/plain;
	charset="Windows-1252"
Content-Transfer-Encoding: 7bit

Hi,

I am not sure this can be done but I'm trying to constrain a sorted set
efficiently using a multicolumn index in postgres. The (simplified)
scenario is this:

CREATE TABLE T
(
  a INT,
  b INT,
  c INT
);

CREATE INDEX t_idx ON T(a,b,c);

Now I can sort using t_idx:

 select * from T order by a,b,c;  -- all good, seq scan using t_idx


I can constrain on a single variable fine:

 select * from T where (a=10 AND b=100 AND c>1000) order by a,b,c;
 -- does seq scan on t_idx and uses the index in the constraint as
expected


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;

then it just does an ordinary filter, and basically does a sequence scan
with no intelligence which isn't great if you've got a table of 20
million items.

Is there any way short of issuing 3 queries and joining them that I can
do this? I had hoped to be able to compare (a,b,c)>(10,100,1000) but of
course that evaluates to (a>10) and (b>100) and (c>1000). It feels like
there should be a simple solution to this... please help :)

Thanks
 Stuart