From: J@Planeti.Biz
Message-ID: <00d801c61c38$52863030$81300d05@fatchubby>
To: <pgsql-performance@postgresql.org>
References: <001401c61bf3$aec1fef0$7f00a8c0@kicommunication.com>
Subject: Re: Multiple Order By Criteria
Date: Wed, 18 Jan 2006 09:06:05 -0500
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1";
	reply-type=original
Content-Transfer-Encoding: 7bit

I have the answer I've been looking for and I'd like to share with all. 
After help from you guys, it appeared that the real issue was using an index 
for my order by X DESC clauses. For some reason that doesn't make good 
sense, postgres doesn't support this, when it kinda should automatically.

Take the following end of an SQL statement.

order by
    col1 DESC
    col2 ASC
    col3 ASC

The first thing I learned is that you need an index that contains all these 
columns in it, in this order. If one of them has DESC then you have to 
create a function / operator class for each data type, in this case let's 
assume it's an int4. So, first thing you do is create a function that you're 
going to use in your operator:

create function
    int4_revcmp(int4,int4)  // --> cal the function whatever you want
    returns int4
    as 'select $2 - $1'
language sql;

Then you make your operator class.
CREATE OPERATOR CLASS int4_revop
      FOR TYPE int4 USING btree AS
          OPERATOR        1       > ,
          OPERATOR        2       >= ,
          OPERATOR        3       = ,
          OPERATOR        4       <= ,
          OPERATOR        5       < ,
          FUNCTION        1       int4_revcmp(int4, int4); // --> must be 
the name of your function you created.

Then when you make your index

create index rev_idx on table
    using btree(
        col1 int4_revop,    // --> must be name of operator class you 
defined.
        col2,
        col3
);

What I don't understand is how to make this function / operator class work 
with a text datatype. I tried interchanging the int4 with char and text and 
postgres didn't like the (as 'select $2 - $1') in the function, which I can 
kinda understand. Since I'm slighlty above my head at this point, I don't 
really know how to do it. Does any smart people here know how ?