Skip site navigation (1) Skip section navigation (2)

Multi ordered select and indexing

From: "Antal Attila" <antal(dot)attila(at)ritek(dot)hu>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: Multi ordered select and indexing
Date: 2004-04-23 13:32:13
Message-ID: 000001c42937$637db7a0$0b02010a@atesz (view raw or flat)
Thread:
Lists: pgsql-sql
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.



Responses

pgsql-sql by date

Next:From: Tom LaneDate: 2004-04-23 14:18:33
Subject: Re: CONTEXT on PL/pgSQL
Previous:From: Martin MarquesDate: 2004-04-23 13:18:51
Subject: CONTEXT on PL/pgSQL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group