Re: Multicolumn order by - Mailing list pgsql-performance

From Theo Kramer
Subject Re: Multicolumn order by
Date
Msg-id 1145426438.3048.25.camel@josh
Whole thread Raw
In response to Re: Multicolumn order by  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: Multicolumn order by
List pgsql-performance
On Wed, 2006-04-19 at 01:08, Tom Lane wrote:
> Theo Kramer <theo@flame.co.za> writes:
> > select * from mytable where
> >   (c1 = 'c1v' and c2 = 'c2v' and c3 >= 'c3v') or
> >   (c1 = 'c1v' and c2 > 'c2v') or
> >   (c1 > 'c1v')
> >   order by c1, c2, c3;
>
> Yeah ... what you really want is the SQL-spec row comparison operator
>
> select ... where (c1,c2,c3) >= ('c1v','c2v','c3v') order by c1,c2,c3;
>
> This does not work properly in any current PG release :-( but it does
> work and is optimized well in CVS HEAD.  See eg this thread
> http://archives.postgresql.org/pgsql-hackers/2006-02/msg00209.php

That is awesome - been fighting with porting my isam based stuff onto
sql for a long time and the row comparison operator is exactly what I
have been looking for.

I tried this on my test system running 8.1.3 and appears to work fine.
Appreciate it if you could let me know in what cases it does not work
properly.

--
Regards
Theo


pgsql-performance by date:

Previous
From: Mark Kirkwood
Date:
Subject: Re: merge>hash>loop
Next
From: Mario Splivalo
Date:
Subject: Re: SELECT FOR UPDATE performance is bad