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 ?
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
