Re: Multi ordered select and indexing

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.
>