Message-ID: <025a01c707eb$83a1b370$cc22a8c0@STU95>
From: "Stuart Brooks" <stuartb@cat.co.za>
To: <pgsql-sql@postgresql.org>
Cc: "Tom Lane" <tgl@sss.pgh.pa.us>
References: <021e01c704b1$ed75eec0$cc22a8c0@STU95>
	<20924.1163171264@sss.pgh.pa.us>
Subject: Re: Constraint on multicolumn index 
Date: Tue, 14 Nov 2006 14:50:49 +0200
Organization: Intervid Technologies
MIME-Version: 1.0
Content-Type: text/plain;
	charset="Windows-1252"
Content-Transfer-Encoding: 7bit

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