From: J@Planeti.Biz
To: pgsql-performance@postgresql.org
Subject: Re: Multiple Order By Criteria
Date: Wed, 18 Jan 2006 09:06:05 -0500
Message-ID: <00d801c61c38$52863030$81300d05@fatchubby> (raw)
References: <001401c61bf3$aec1fef0$7f00a8c0@kicommunication.com>
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 ?
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-performance@postgresql.org
Cc: J@Planeti.Biz
Subject: Re: Multiple Order By Criteria
In-Reply-To: <00d801c61c38$52863030$81300d05@fatchubby>
* 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