From: | "Stuart Brooks" <stuartb(at)cat(dot)co(dot)za> |
---|---|
To: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | Constraint on multicolumn index |
Date: | 2006-11-10 10:21:02 |
Message-ID: | 021e01c704b1$ed75eec0$cc22a8c0@STU95 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Ezequias Rodrigues da Rocha | 2006-11-10 14:28:53 | Between and miliseconds (timestamps) |
Previous Message | Richard Huxton | 2006-11-09 10:19:47 | Re: [SQL] [ADMIN] Is there anyway to... |