Thread: Constraint on multicolumn index

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

"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

> > 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

Thanks for the response. PG 8.2 looks like a good option when it is
finalized (I see it is beta 3 so shouldn't be too long should it?), but
I also need to have a closer look at the row constructor - this is a new
one for me as I have been using MySQL up til now.

The best solution I could come up with for my problem was to do a
union - something like:

(SELECT * from T WHERE (a=10 AND b=100 AND c>1000) ORDER BY a,b,c LIMIT
10)
UNION
(SELECT * from T WHERE (a=10 AND b>100) ORDER BY a,b,c LIMIT 10)
UNION
(SELECT * from T WHERE (a>10) ORDER BY a,b,c LIMIT 10)
ORDER BY a,b,c LIMIT 10;

which would use an index for each of the selects and then have to merge,
sort and limit the results. This seemed to work although it gets clumsy
if there are a whole lot of extra criteria.

Thanks again for the help,Stuart