Re: A question on the query planner - Mailing list pgsql-performance

> Hmmm ... [squints] ... it's not supposed to do that ...

The attached patch seems to make it better.

            regards, tom lane


Index: src/backend/optimizer/path/costsize.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/optimizer/path/costsize.c,v
retrieving revision 1.115
diff -c -r1.115 costsize.c
*** src/backend/optimizer/path/costsize.c    5 Oct 2003 22:44:25 -0000    1.115
--- src/backend/optimizer/path/costsize.c    3 Dec 2003 17:40:58 -0000
***************
*** 1322,1327 ****
--- 1322,1331 ----
      float4       *numbers;
      int            nnumbers;

+     /* Ignore any binary-compatible relabeling */
+     if (var && IsA(var, RelabelType))
+         var = (Var *) ((RelabelType *) var)->arg;
+
      /*
       * Lookup info about var's relation and attribute; if none available,
       * return default estimate.
Index: src/backend/optimizer/path/pathkeys.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/optimizer/path/pathkeys.c,v
retrieving revision 1.53
diff -c -r1.53 pathkeys.c
*** src/backend/optimizer/path/pathkeys.c    4 Aug 2003 02:40:00 -0000    1.53
--- src/backend/optimizer/path/pathkeys.c    3 Dec 2003 17:40:58 -0000
***************
*** 25,36 ****
  #include "optimizer/tlist.h"
  #include "optimizer/var.h"
  #include "parser/parsetree.h"
  #include "parser/parse_func.h"
  #include "utils/lsyscache.h"
  #include "utils/memutils.h"


! static PathKeyItem *makePathKeyItem(Node *key, Oid sortop);
  static List *make_canonical_pathkey(Query *root, PathKeyItem *item);
  static Var *find_indexkey_var(Query *root, RelOptInfo *rel,
                    AttrNumber varattno);
--- 25,37 ----
  #include "optimizer/tlist.h"
  #include "optimizer/var.h"
  #include "parser/parsetree.h"
+ #include "parser/parse_expr.h"
  #include "parser/parse_func.h"
  #include "utils/lsyscache.h"
  #include "utils/memutils.h"


! static PathKeyItem *makePathKeyItem(Node *key, Oid sortop, bool checkType);
  static List *make_canonical_pathkey(Query *root, PathKeyItem *item);
  static Var *find_indexkey_var(Query *root, RelOptInfo *rel,
                    AttrNumber varattno);
***************
*** 41,50 ****
   *        create a PathKeyItem node
   */
  static PathKeyItem *
! makePathKeyItem(Node *key, Oid sortop)
  {
      PathKeyItem *item = makeNode(PathKeyItem);

      item->key = key;
      item->sortop = sortop;
      return item;
--- 42,70 ----
   *        create a PathKeyItem node
   */
  static PathKeyItem *
! makePathKeyItem(Node *key, Oid sortop, bool checkType)
  {
      PathKeyItem *item = makeNode(PathKeyItem);

+     /*
+      * Some callers pass expressions that are not necessarily of the same
+      * type as the sort operator expects as input (for example when dealing
+      * with an index that uses binary-compatible operators).  We must relabel
+      * these with the correct type so that the key expressions will be seen
+      * as equal() to expressions that have been correctly labeled.
+      */
+     if (checkType)
+     {
+         Oid            lefttype,
+                     righttype;
+
+         op_input_types(sortop, &lefttype, &righttype);
+         if (exprType(key) != lefttype)
+             key = (Node *) makeRelabelType((Expr *) key,
+                                            lefttype, -1,
+                                            COERCE_DONTCARE);
+     }
+
      item->key = key;
      item->sortop = sortop;
      return item;
***************
*** 70,78 ****
  {
      Expr       *clause = restrictinfo->clause;
      PathKeyItem *item1 = makePathKeyItem(get_leftop(clause),
!                                          restrictinfo->left_sortop);
      PathKeyItem *item2 = makePathKeyItem(get_rightop(clause),
!                                          restrictinfo->right_sortop);
      List       *newset,
                 *cursetlink;

--- 90,100 ----
  {
      Expr       *clause = restrictinfo->clause;
      PathKeyItem *item1 = makePathKeyItem(get_leftop(clause),
!                                          restrictinfo->left_sortop,
!                                          false);
      PathKeyItem *item2 = makePathKeyItem(get_rightop(clause),
!                                          restrictinfo->right_sortop,
!                                          false);
      List       *newset,
                 *cursetlink;

***************
*** 668,674 ****
          }

          /* OK, make a sublist for this sort key */
!         item = makePathKeyItem(indexkey, sortop);
          cpathkey = make_canonical_pathkey(root, item);

          /*
--- 690,696 ----
          }

          /* OK, make a sublist for this sort key */
!         item = makePathKeyItem(indexkey, sortop, true);
          cpathkey = make_canonical_pathkey(root, item);

          /*
***************
*** 785,791 ****
                                          tle->resdom->restypmod,
                                          0);
                      outer_item = makePathKeyItem((Node *) outer_var,
!                                                  sub_item->sortop);
                      /* score = # of mergejoin peers */
                      score = count_canonical_peers(root, outer_item);
                      /* +1 if it matches the proper query_pathkeys item */
--- 807,814 ----
                                          tle->resdom->restypmod,
                                          0);
                      outer_item = makePathKeyItem((Node *) outer_var,
!                                                  sub_item->sortop,
!                                                  true);
                      /* score = # of mergejoin peers */
                      score = count_canonical_peers(root, outer_item);
                      /* +1 if it matches the proper query_pathkeys item */
***************
*** 893,899 ****
          PathKeyItem *pathkey;

          sortkey = get_sortgroupclause_expr(sortcl, tlist);
!         pathkey = makePathKeyItem(sortkey, sortcl->sortop);

          /*
           * The pathkey becomes a one-element sublist, for now;
--- 916,922 ----
          PathKeyItem *pathkey;

          sortkey = get_sortgroupclause_expr(sortcl, tlist);
!         pathkey = makePathKeyItem(sortkey, sortcl->sortop, true);

          /*
           * The pathkey becomes a one-element sublist, for now;
***************
*** 937,943 ****
      {
          oldcontext = MemoryContextSwitchTo(GetMemoryChunkContext(restrictinfo));
          key = get_leftop(restrictinfo->clause);
!         item = makePathKeyItem(key, restrictinfo->left_sortop);
          restrictinfo->left_pathkey = make_canonical_pathkey(root, item);
          MemoryContextSwitchTo(oldcontext);
      }
--- 960,966 ----
      {
          oldcontext = MemoryContextSwitchTo(GetMemoryChunkContext(restrictinfo));
          key = get_leftop(restrictinfo->clause);
!         item = makePathKeyItem(key, restrictinfo->left_sortop, false);
          restrictinfo->left_pathkey = make_canonical_pathkey(root, item);
          MemoryContextSwitchTo(oldcontext);
      }
***************
*** 945,951 ****
      {
          oldcontext = MemoryContextSwitchTo(GetMemoryChunkContext(restrictinfo));
          key = get_rightop(restrictinfo->clause);
!         item = makePathKeyItem(key, restrictinfo->right_sortop);
          restrictinfo->right_pathkey = make_canonical_pathkey(root, item);
          MemoryContextSwitchTo(oldcontext);
      }
--- 968,974 ----
      {
          oldcontext = MemoryContextSwitchTo(GetMemoryChunkContext(restrictinfo));
          key = get_rightop(restrictinfo->clause);
!         item = makePathKeyItem(key, restrictinfo->right_sortop, false);
          restrictinfo->right_pathkey = make_canonical_pathkey(root, item);
          MemoryContextSwitchTo(oldcontext);
      }
Index: src/backend/utils/cache/lsyscache.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/utils/cache/lsyscache.c,v
retrieving revision 1.108
diff -c -r1.108 lsyscache.c
*** src/backend/utils/cache/lsyscache.c    4 Oct 2003 18:22:59 -0000    1.108
--- src/backend/utils/cache/lsyscache.c    3 Dec 2003 17:40:58 -0000
***************
*** 465,470 ****
--- 465,493 ----
  }

  /*
+  * op_input_types
+  *
+  *        Returns the left and right input datatypes for an operator
+  *        (InvalidOid if not relevant).
+  */
+ void
+ op_input_types(Oid opno, Oid *lefttype, Oid *righttype)
+ {
+     HeapTuple    tp;
+     Form_pg_operator optup;
+
+     tp = SearchSysCache(OPEROID,
+                         ObjectIdGetDatum(opno),
+                         0, 0, 0);
+     if (!HeapTupleIsValid(tp))    /* shouldn't happen */
+         elog(ERROR, "cache lookup failed for operator %u", opno);
+     optup = (Form_pg_operator) GETSTRUCT(tp);
+     *lefttype = optup->oprleft;
+     *righttype = optup->oprright;
+     ReleaseSysCache(tp);
+ }
+
+ /*
   * op_mergejoinable
   *
   *        Returns the left and right sort operators corresponding to a
Index: src/include/utils/lsyscache.h
===================================================================
RCS file: /cvsroot/pgsql-server/src/include/utils/lsyscache.h,v
retrieving revision 1.82
diff -c -r1.82 lsyscache.h
*** src/include/utils/lsyscache.h    4 Oct 2003 18:22:59 -0000    1.82
--- src/include/utils/lsyscache.h    3 Dec 2003 17:41:00 -0000
***************
*** 40,45 ****
--- 40,46 ----
  extern bool opclass_is_hash(Oid opclass);
  extern RegProcedure get_opcode(Oid opno);
  extern char *get_opname(Oid opno);
+ extern void op_input_types(Oid opno, Oid *lefttype, Oid *righttype);
  extern bool op_mergejoinable(Oid opno, Oid *leftOp, Oid *rightOp);
  extern void op_mergejoin_crossops(Oid opno, Oid *ltop, Oid *gtop,
                        RegProcedure *ltproc, RegProcedure *gtproc);