pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Multi ordered select and indexing
6+ messages / 5 participants
[nested] [flat]
* Multi ordered select and indexing
@ 2004-04-23 13:32 Antal Attila <antal.attila@ritek.hu>
1970-01-01 00:00 ` Re: Multi ordered select and indexing Christoph Haller <ch@rodos.fzk.de>
2004-04-29 19:34 ` Re: Multi ordered select and indexing Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 6+ messages in thread
From: Antal Attila @ 2004-04-23 13:32 UTC (permalink / raw)
To: 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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Multi ordered select and indexing
2004-04-23 13:32 Multi ordered select and indexing Antal Attila <antal.attila@ritek.hu>
@ 1970-01-01 00:00 ` Christoph Haller <ch@rodos.fzk.de>
1 sibling, 0 replies; 6+ messages in thread
From: Christoph Haller @ 1970-01-01 00:00 UTC (permalink / raw)
To: antal.attila@ritek.hu; +Cc: pgsql-sql
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.
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Multi ordered select and indexing
2004-04-23 13:32 Multi ordered select and indexing Antal Attila <antal.attila@ritek.hu>
@ 2004-04-29 19:34 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 6+ messages in thread
From: Tom Lane @ 2004-04-29 19:34 UTC (permalink / raw)
To: Antal Attila <antal.attila@ritek.hu>; +Cc: pgsql-sql
"Antal Attila" <antal.attila@ritek.hu> writes:
> 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);
This is the wrong way to go about it. A useful descending-order opclass
simply rearranges the logical relationships of the standard comparison
operators. You do need a new comparison function, but nothing else:
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);
Now you can just use ASC/DESC in your ORDER BY ...
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Multi ordered select and indexing
@ 2004-04-23 14:33 Antal Attila <antal.attila@ritek.hu>
2004-04-23 16:41 ` Re: Multi ordered select and indexing Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-04-23 17:37 ` Re: Multi ordered select and indexing Bruno Wolff III <bruno@wolff.to>
0 siblings, 2 replies; 6+ messages in thread
From: Antal Attila @ 2004-04-23 14:33 UTC (permalink / raw)
To: pgsql-sql
Hi!
We have a complex problematic area. What is the simplest solution for
the next 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, becouse there are different directions after
ORDER BY. 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 can 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)
An other problem: we have to replace the operators after the WHERE
conditions, if that contains conditions 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. But 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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Multi ordered select and indexing
2004-04-23 14:33 Multi ordered select and indexing Antal Attila <antal.attila@ritek.hu>
@ 2004-04-23 16:41 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
1 sibling, 0 replies; 6+ messages in thread
From: Stephan Szabo @ 2004-04-23 16:41 UTC (permalink / raw)
To: Antal Attila <antal.attila@ritek.hu>; +Cc: pgsql-sql
On Fri, 23 Apr 2004, Antal Attila wrote:
> Hi!
>
> We have a complex problematic area. What is the simplest solution for
> the next 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, becouse there are different directions after
> ORDER BY. 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"?
Yes. You should be able to make an opclass where the operators are the
standard operators but in a different order which should get used when
doing DESC. I think there should be examples in the archives.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Multi ordered select and indexing
2004-04-23 14:33 Multi ordered select and indexing Antal Attila <antal.attila@ritek.hu>
@ 2004-04-23 17:37 ` Bruno Wolff III <bruno@wolff.to>
1 sibling, 0 replies; 6+ messages in thread
From: Bruno Wolff III @ 2004-04-23 17:37 UTC (permalink / raw)
To: Antal Attila <antal.attila@ritek.hu>; +Cc: pgsql-sql
On Fri, Apr 23, 2004 at 16:33:14 +0200,
Antal Attila <antal.attila@ritek.hu> wrote:
>
> In our experience, postgres cannot use a multi-colum index on (col1,
> col2) in this situation, becouse there are different directions after
> ORDER BY. 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"?
Another option you might have is using functional indexes. If you are using
7.4.x or greater and one of the columns has a reasonable - operator
(pretty much this is the numeric types) then you can have an index on
(col1, (-col2)) and then order by col1, -col2.
The main advantage is that this is simpler than making a new opclass.
If you have already gone to that trouble it may be better to stick with it.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2004-04-29 19:34 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-04-23 13:32 Multi ordered select and indexing Antal Attila <antal.attila@ritek.hu>
1970-01-01 00:00 ` Christoph Haller <ch@rodos.fzk.de>
2004-04-29 19:34 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-04-23 14:33 Multi ordered select and indexing Antal Attila <antal.attila@ritek.hu>
2004-04-23 16:41 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-04-23 17:37 ` Bruno Wolff III <bruno@wolff.to>
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