From: Christoph Haller <ch@rodos.fzk.de>
Message-Id: <200404291135.NAA07447@rodos>
Subject: Re: Multi ordered select and indexing
To: antal.attila@ritek.hu (Antal Attila)
Date: Thu, 29 Apr 2004 13:35:38 METDST
Cc: pgsql-sql@postgresql.org
In-Reply-To: <000001c42937$637db7a0$0b02010a@atesz>;
	from "Antal Attila" at Apr 26, 104 4:40 pm

Have you thought of using a functional index on both columns? 
Regards, Christoph 

> 
> Hi!  
> What is the simplest solution for this query type:  
> 
>   SELECT * FROM tablename ORDER BY col1 ASC, col2 DESC;  
> 
> In our experience, postgres cannot use a multi-colum index on (col1,
> col2) in this situation. Is custom operator class the easiest solution,
> which can solve the reverse indexing on col2? Our problem with this
> solution, is that we have to replace "DESC" with "USING myoperator". Is
> it possible, that postgres can recognize "myoperator" without replacing
> "DESC"?  
> We made new operators on int4 type starting with letter "/":  
> 
>   CREATE OPERATOR CLASS int4_reverse_order_ops  
>     FOR TYPE int4 USING btree AS  
>         OPERATOR        1       /< ,  
>         OPERATOR        2       /<= ,  
>         OPERATOR        3       /= ,  
>         OPERATOR        4       />= ,  
>         OPERATOR        5       /> ,  
>         FUNCTION        1       int4_reverse_order_cmp(int4, int4);  
>   
> Create an index:  
>   CREATE INDEX idx_test ON tablename (col1, col2
> int4_reverse_order_ops);  
>   
> Postgres use this index in this query:  
> EXPLAIN SELECT * FROM tablename ORDER BY col1 ASC, col2 USING /< limit
> 10;  
>                                      QUERY PLAN  
> ------------------------------------------------------------------------
> ------------  
>  Limit  (cost=0.00..0.52 rows=10 width=8)  
>    ->  Index Scan using idx_test on tablename  (cost=0.00..52.00
> rows=1000 width=8)  
>   
> Another problem: we have to replace the operators in WHERE conditions,
> if that contains condition on col2.  
> EXPLAIN SELECT * FROM tablename WHERE col1 < 10 and col2 < 10 ORDER BY
> col1, col2 using /< limit 10;  
>                                     QUERY PLAN  
> ------------------------------------------------------------------------
> -----------  
>  Limit  (cost=0.00..4.14 rows=10 width=8)  
>    ->  Index Scan using idx_test on tablename  (cost=0.00..46.33
> rows=112 width=8)  
>          Index Cond: (col1 < 10)  
>          Filter: (col2 < 10)  
> 
> You can see, it use filtering on col2, but in the next case it can
> indexing on col2 condition:  
> EXPLAIN SELECT * FROM tablename WHERE col1 < 10 and col2 /< 10 ORDER BY
> col1, col2 using /< limit 10;  
>                                     QUERY PLAN  
> ------------------------------------------------------------------------
> -----------  
>  Limit  (cost=0.00..3.82 rows=10 width=8)  
>    ->  Index Scan using idx_test on tablename  (cost=0.00..42.78
> rows=112 width=8)  
>          Index Cond: ((col1 < 10) AND (col2 /< 10))  
> 
> Can we do this easier? If can, how?  
>   
> After that, we have an other unsolved problem, if the col2's type is
> TEXT, and we try to use a LIKE operator on it. We coludn't replace the
> LIKE with own operator, because postgres exchange the "LIKE" with an
> expression which contains ">=" and "<". We made own like operator:
> "/~~", but we cannot tell postgres to use our own "/>=" and '/<'
> operators instead of "/~~".  
> CREATE OPERATOR /~~ (  
>    leftarg = text, rightarg = text, procedure = textlike,  
>    commutator = /~~ , negator = !~~ ,  
>    restrict = scalarltsel, join = scalarltjoinsel  
> );  
>   
> Thanks in advance.
>