kevin wrote:
Question:
I have a question about using index in order statement.
Why index ix_2 work by Seq Scan and index ix_3 work by Index Scan.
Example :
ix_2 condition :
When I try
explain
select * from a_test
order by code_ desc
Postgresql response
Sort (cost=100001815.08..100001852.56 rows=14990 width=56)
Sort Key: code_
-> Seq Scan on a_test (cost=100000000.00..100000260.90 rows=14990
width=56)
ix_3 condition :
When I try
explain
select * from a_test
order by lower(code_) desc
Postgresql response
Index Scan using ix_3 on a_test (cost=0.00..769.27 rows=14990 width=18)
Thanks for the report. This seems to have been broken by this patch back
in May:
http://archives.postgresql.org/pgsql-committers/2007-05/msg00394.php
that wraps pathkey expressions with a relabel node. Because of that,
get_eclass_for_sort_expr doesn't recognize that the ordering of the
index matches that of the query.
Attached is a patch that fixes that test case. I'm not very familiar
with that piece of code, though, and I have a sneaking suspicion that
the patch is either not general enough, there may be other places where
we should ignore relabel nodes, or it brakes something else.
I'm surprised this hasn't been noticed before. It doesn't happen with
text datatype, but varchar is very common datatype as well.
PS. Kevin, in the future, please specify which PostgreSQL version you're
using. The fact that the above DDL statements don't work until 8.3beta
releases gave it away this time :-).
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
Index: src/backend/optimizer/path/equivclass.c
===================================================================
RCS file: /home/hlinnaka/pgcvsrepository/pgsql/src/backend/optimizer/path/equivclass.c,v
retrieving revision 1.3
diff -c -r1.3 equivclass.c
*** src/backend/optimizer/path/equivclass.c 7 Jul 2007 20:46:45 -0000 1.3
--- src/backend/optimizer/path/equivclass.c 2 Nov 2007 11:48:12 -0000
***************
*** 373,378 ****
--- 373,388 ----
EquivalenceMember *newem;
ListCell *lc1;
MemoryContext oldcontext;
+ Expr *stripped_expr;
+
+ /*
+ * Strip any relabel nodes first; they're not meaningful
+ * for ordering purposes.
+ */
+ if (IsA(expr, RelabelType))
+ stripped_expr = ((RelabelType *)expr)->arg;
+ else
+ stripped_expr = expr;
/*
* Scan through the existing EquivalenceClasses for a match
***************
*** 390,395 ****
--- 400,406 ----
foreach(lc2, cur_ec->ec_members)
{
EquivalenceMember *cur_em = (EquivalenceMember *) lfirst(lc2);
+ Expr *em_expr;
/*
* If below an outer join, don't match constants: they're not
***************
*** 399,406 ****
cur_em->em_is_const)
continue;
if (expr_datatype == cur_em->em_datatype &&
! equal(expr, cur_em->em_expr))
return cur_ec; /* Match! */
}
}
--- 410,421 ----
cur_em->em_is_const)
continue;
+ em_expr = cur_em->em_expr;
+ if (IsA(em_expr, RelabelType))
+ em_expr = ((RelabelType *)em_expr)->arg;
+
if (expr_datatype == cur_em->em_datatype &&
! equal(stripped_expr, em_expr))
return cur_ec; /* Match! */
}
}
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
