Re: [BUGS] It is a bug in pred_test()! (Was: Please, HELP! Why is the query plan so wrong???) - Mailing list pgsql-sql

Dmitry Tkach <dmitry@openratings.com> writes:
> It now looks like a bug in the query planner to me - it seems that it
> just doesn't consider indices with predicates for join plans...
> I was looking at the source code, and it looks like pred_test() is
> responsible for that.

Yup.  I've applied the attached patch, which seems to solve the problem
in CVS tip.  I haven't tested it in the REL7_2 branch, but I believe it
will work if you want to patch locally.

            regards, tom lane

*** src/backend/optimizer/path/indxpath.c.orig    Fri Jun 21 14:17:33 2002
--- src/backend/optimizer/path/indxpath.c    Sat Jul 13 14:57:26 2002
***************
*** 35,40 ****
--- 35,41 ----
  #include "parser/parse_coerce.h"
  #include "parser/parse_expr.h"
  #include "parser/parse_oper.h"
+ #include "rewrite/rewriteManip.h"
  #include "utils/builtins.h"
  #include "utils/fmgroids.h"
  #include "utils/lsyscache.h"
***************
*** 79,85 ****
                           int indexkey, Oid opclass,
                           Expr *clause, bool join);
  static bool pred_test(List *predicate_list, List *restrictinfo_list,
!           List *joininfo_list);
  static bool pred_test_restrict_list(Expr *predicate, List *restrictinfo_list);
  static bool pred_test_recurse_clause(Expr *predicate, Node *clause);
  static bool pred_test_recurse_pred(Expr *predicate, Node *clause);
--- 80,86 ----
                           int indexkey, Oid opclass,
                           Expr *clause, bool join);
  static bool pred_test(List *predicate_list, List *restrictinfo_list,
!           List *joininfo_list, int relvarno);
  static bool pred_test_restrict_list(Expr *predicate, List *restrictinfo_list);
  static bool pred_test_recurse_clause(Expr *predicate, Node *clause);
  static bool pred_test_recurse_pred(Expr *predicate, Node *clause);
***************
*** 153,159 ****
           * predicate test.
           */
          if (index->indpred != NIL)
!             if (!pred_test(index->indpred, restrictinfo_list, joininfo_list))
                  continue;

          /*
--- 154,161 ----
           * predicate test.
           */
          if (index->indpred != NIL)
!             if (!pred_test(index->indpred, restrictinfo_list, joininfo_list,
!                            lfirsti(rel->relids)))
                  continue;

          /*
***************
*** 957,963 ****
   *      to CNF format). --Nels, Jan '93
   */
  static bool
! pred_test(List *predicate_list, List *restrictinfo_list, List *joininfo_list)
  {
      List       *pred;

--- 959,966 ----
   *      to CNF format). --Nels, Jan '93
   */
  static bool
! pred_test(List *predicate_list, List *restrictinfo_list, List *joininfo_list,
!           int relvarno)
  {
      List       *pred;

***************
*** 979,984 ****
--- 982,999 ----
      if (restrictinfo_list == NIL)
          return false;            /* no restriction clauses: the test must
                                   * fail */
+
+     /*
+      * The predicate as stored in the index definition will use varno 1
+      * for its Vars referencing the indexed relation.  If the indexed
+      * relation isn't varno 1 in the query, we must adjust the predicate
+      * to make the Vars match, else equal() won't work.
+      */
+     if (relvarno != 1)
+     {
+         predicate_list = copyObject(predicate_list);
+         ChangeVarNodes((Node *) predicate_list, 1, relvarno, 0);
+     }

      foreach(pred, predicate_list)
      {