pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Greg Stark <gsstark@mit.edu>
To: Jared Carr <jared@89glass.com>
To: pgsql-performance@postgresql.org
Subject: Re: A question on the query planner
Date: Wed, 03 Dec 2003 12:49:06 -0500
Message-ID: <2704.1070473746@sss.pgh.pa.us> (raw)
In-Reply-To: <26699.1070423607@sss.pgh.pa.us>
References: <3FCBB639.10409@89glass.com>
	<1070385381.24915.8650.camel@camel>
	<3FCCD225.8030609@89glass.com>
	<87d6b79har.fsf@stark.dyndns.tv>
	<3FCCE48B.10406@89glass.com>
	<871xrn9e09.fsf@stark.dyndns.tv>
	<3FCCF61E.90307@89glass.com>
	<87ptf697h0.fsf@stark.dyndns.tv>
	<25476.1070409047@sss.pgh.pa.us>
	<87znea7kis.fsf@stark.dyndns.tv>
	<26699.1070423607@sss.pgh.pa.us>

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

The attached patch seems to make it better.

			regards, tom lane


inline patch 1: src/backend/optimizer/path/costsize.c, src/backend/optimizer/path/pathkeys.c, src/backend/utils/cache/lsyscache.c and 1 more (+60/-9 lines) download inline-1.patchIndex: 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);

view thread (19+ messages)  latest in thread

Message-ID: <2704.1070473746@sss.pgh.pa.us>
Permalink:  ../2704.1070473746@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/2704.1070473746@sss.pgh.pa.us

 ·  · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: tgl@sss.pgh.pa.us, gsstark@mit.edu, jared@89glass.com
  Subject: Re: A question on the query planner
  In-Reply-To: <2704.1070473746@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox