Re: I have a question about using index in order statement.

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! */
          }
      }