Re: A question on the query planner

> 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);