From: Christoph Haller <ch@rodos.fzk.de>
To: antal.attila@ritek.hu
Cc: pgsql-sql@postgresql.org
Subject: Re: Multi ordered select and indexing
Date: Thu, 29 Apr 2004 13:35:38 METDST
Message-ID: <200404291135.NAA07447@rodos> (raw)
In-Reply-To: <000001c42937$637db7a0$0b02010a@atesz>
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.
>
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-sql@postgresql.org
Cc: ch@rodos.fzk.de, antal.attila@ritek.hu
Subject: Re: Multi ordered select and indexing
In-Reply-To: <200404291135.NAA07447@rodos>
* 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