From: Stuart Brooks <stuartb@cat.co.za>
To: pgsql-sql@postgresql.org
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: Constraint on multicolumn index
Date: Tue, 14 Nov 2006 14:50:49 +0200
Message-ID: <025a01c707eb$83a1b370$cc22a8c0@STU95> (raw)
References: <021e01c704b1$ed75eec0$cc22a8c0@STU95>
<20924.1163171264@sss.pgh.pa.us>
> > 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: stuartb@cat.co.za, tgl@sss.pgh.pa.us
Subject: Re: Constraint on multicolumn index
In-Reply-To: <025a01c707eb$83a1b370$cc22a8c0@STU95>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox