Constraint on multicolumn index
От | Stuart Brooks |
---|---|
Тема | Constraint on multicolumn index |
Дата | |
Msg-id | 021e01c704b1$ed75eec0$cc22a8c0@STU95 обсуждение исходный текст |
Ответы |
Re: Constraint on multicolumn index
|
Список | 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 constraintas expected But if I want the next item following t=(a=10,b=100,c=1000): select * from Twhere (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 :) ThanksStuart
В списке pgsql-sql по дате отправления: