Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit7f8f766

Browse files
committed
Planner failed to be smart about binary-compatible expressions in pathkeys
and hash bucket-size estimation. Issue has been there awhile but is morecritical in 7.4 because it affects varchar columns. Per report fromGreg Stark.
1 parent32580ef commit7f8f766

File tree

4 files changed

+64
-13
lines changed

4 files changed

+64
-13
lines changed

‎src/backend/optimizer/path/costsize.c‎

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -49,7 +49,7 @@
4949
* Portions Copyright (c) 1994, Regents of the University of California
5050
*
5151
* IDENTIFICATION
52-
* $PostgreSQL: pgsql/src/backend/optimizer/path/costsize.c,v 1.116 2003/11/29 19:51:50 pgsql Exp $
52+
* $PostgreSQL: pgsql/src/backend/optimizer/path/costsize.c,v 1.117 2003/12/03 17:45:07 tgl Exp $
5353
*
5454
*-------------------------------------------------------------------------
5555
*/
@@ -1322,6 +1322,10 @@ estimate_hash_bucketsize(Query *root, Var *var, int nbuckets)
13221322
float4*numbers;
13231323
intnnumbers;
13241324

1325+
/* Ignore any binary-compatible relabeling */
1326+
if (var&&IsA(var,RelabelType))
1327+
var= (Var*) ((RelabelType*)var)->arg;
1328+
13251329
/*
13261330
* Lookup info about var's relation and attribute; if none available,
13271331
* return default estimate.

‎src/backend/optimizer/path/pathkeys.c‎

Lines changed: 33 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -11,7 +11,7 @@
1111
* Portions Copyright (c) 1994, Regents of the University of California
1212
*
1313
* IDENTIFICATION
14-
* $PostgreSQL: pgsql/src/backend/optimizer/path/pathkeys.c,v 1.54 2003/11/29 19:51:50 pgsql Exp $
14+
* $PostgreSQL: pgsql/src/backend/optimizer/path/pathkeys.c,v 1.55 2003/12/03 17:45:07 tgl Exp $
1515
*
1616
*-------------------------------------------------------------------------
1717
*/
@@ -25,12 +25,13 @@
2525
#include"optimizer/tlist.h"
2626
#include"optimizer/var.h"
2727
#include"parser/parsetree.h"
28+
#include"parser/parse_expr.h"
2829
#include"parser/parse_func.h"
2930
#include"utils/lsyscache.h"
3031
#include"utils/memutils.h"
3132

3233

33-
staticPathKeyItem*makePathKeyItem(Node*key,Oidsortop);
34+
staticPathKeyItem*makePathKeyItem(Node*key,Oidsortop,boolcheckType);
3435
staticList*make_canonical_pathkey(Query*root,PathKeyItem*item);
3536
staticVar*find_indexkey_var(Query*root,RelOptInfo*rel,
3637
AttrNumbervarattno);
@@ -41,10 +42,29 @@ static Var *find_indexkey_var(Query *root, RelOptInfo *rel,
4142
*create a PathKeyItem node
4243
*/
4344
staticPathKeyItem*
44-
makePathKeyItem(Node*key,Oidsortop)
45+
makePathKeyItem(Node*key,Oidsortop,boolcheckType)
4546
{
4647
PathKeyItem*item=makeNode(PathKeyItem);
4748

49+
/*
50+
* Some callers pass expressions that are not necessarily of the same
51+
* type as the sort operator expects as input (for example when dealing
52+
* with an index that uses binary-compatible operators). We must relabel
53+
* these with the correct type so that the key expressions will be seen
54+
* as equal() to expressions that have been correctly labeled.
55+
*/
56+
if (checkType)
57+
{
58+
Oidlefttype,
59+
righttype;
60+
61+
op_input_types(sortop,&lefttype,&righttype);
62+
if (exprType(key)!=lefttype)
63+
key= (Node*)makeRelabelType((Expr*)key,
64+
lefttype,-1,
65+
COERCE_DONTCARE);
66+
}
67+
4868
item->key=key;
4969
item->sortop=sortop;
5070
returnitem;
@@ -70,9 +90,11 @@ add_equijoined_keys(Query *root, RestrictInfo *restrictinfo)
7090
{
7191
Expr*clause=restrictinfo->clause;
7292
PathKeyItem*item1=makePathKeyItem(get_leftop(clause),
73-
restrictinfo->left_sortop);
93+
restrictinfo->left_sortop,
94+
false);
7495
PathKeyItem*item2=makePathKeyItem(get_rightop(clause),
75-
restrictinfo->right_sortop);
96+
restrictinfo->right_sortop,
97+
false);
7698
List*newset,
7799
*cursetlink;
78100

@@ -668,7 +690,7 @@ build_index_pathkeys(Query *root,
668690
}
669691

670692
/* OK, make a sublist for this sort key */
671-
item=makePathKeyItem(indexkey,sortop);
693+
item=makePathKeyItem(indexkey,sortop, true);
672694
cpathkey=make_canonical_pathkey(root,item);
673695

674696
/*
@@ -785,7 +807,8 @@ build_subquery_pathkeys(Query *root, RelOptInfo *rel, Query *subquery)
785807
tle->resdom->restypmod,
786808
0);
787809
outer_item=makePathKeyItem((Node*)outer_var,
788-
sub_item->sortop);
810+
sub_item->sortop,
811+
true);
789812
/* score = # of mergejoin peers */
790813
score=count_canonical_peers(root,outer_item);
791814
/* +1 if it matches the proper query_pathkeys item */
@@ -893,7 +916,7 @@ make_pathkeys_for_sortclauses(List *sortclauses,
893916
PathKeyItem*pathkey;
894917

895918
sortkey=get_sortgroupclause_expr(sortcl,tlist);
896-
pathkey=makePathKeyItem(sortkey,sortcl->sortop);
919+
pathkey=makePathKeyItem(sortkey,sortcl->sortop, true);
897920

898921
/*
899922
* The pathkey becomes a one-element sublist, for now;
@@ -937,15 +960,15 @@ cache_mergeclause_pathkeys(Query *root, RestrictInfo *restrictinfo)
937960
{
938961
oldcontext=MemoryContextSwitchTo(GetMemoryChunkContext(restrictinfo));
939962
key=get_leftop(restrictinfo->clause);
940-
item=makePathKeyItem(key,restrictinfo->left_sortop);
963+
item=makePathKeyItem(key,restrictinfo->left_sortop, false);
941964
restrictinfo->left_pathkey=make_canonical_pathkey(root,item);
942965
MemoryContextSwitchTo(oldcontext);
943966
}
944967
if (restrictinfo->right_pathkey==NIL)
945968
{
946969
oldcontext=MemoryContextSwitchTo(GetMemoryChunkContext(restrictinfo));
947970
key=get_rightop(restrictinfo->clause);
948-
item=makePathKeyItem(key,restrictinfo->right_sortop);
971+
item=makePathKeyItem(key,restrictinfo->right_sortop, false);
949972
restrictinfo->right_pathkey=make_canonical_pathkey(root,item);
950973
MemoryContextSwitchTo(oldcontext);
951974
}

‎src/backend/utils/cache/lsyscache.c‎

Lines changed: 24 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1994, Regents of the University of California
88
*
99
* IDENTIFICATION
10-
* $PostgreSQL: pgsql/src/backend/utils/cache/lsyscache.c,v 1.111 2003/11/29 19:52:00 pgsql Exp $
10+
* $PostgreSQL: pgsql/src/backend/utils/cache/lsyscache.c,v 1.112 2003/12/03 17:45:09 tgl Exp $
1111
*
1212
* NOTES
1313
* Eventually, the index information should go through here, too.
@@ -465,6 +465,29 @@ get_opname(Oid opno)
465465
returnNULL;
466466
}
467467

468+
/*
469+
* op_input_types
470+
*
471+
*Returns the left and right input datatypes for an operator
472+
*(InvalidOid if not relevant).
473+
*/
474+
void
475+
op_input_types(Oidopno,Oid*lefttype,Oid*righttype)
476+
{
477+
HeapTupletp;
478+
Form_pg_operatoroptup;
479+
480+
tp=SearchSysCache(OPEROID,
481+
ObjectIdGetDatum(opno),
482+
0,0,0);
483+
if (!HeapTupleIsValid(tp))/* shouldn't happen */
484+
elog(ERROR,"cache lookup failed for operator %u",opno);
485+
optup= (Form_pg_operator)GETSTRUCT(tp);
486+
*lefttype=optup->oprleft;
487+
*righttype=optup->oprright;
488+
ReleaseSysCache(tp);
489+
}
490+
468491
/*
469492
* op_mergejoinable
470493
*

‎src/include/utils/lsyscache.h‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -6,7 +6,7 @@
66
* Portions Copyright (c) 1996-2003, PostgreSQL Global Development Group
77
* Portions Copyright (c) 1994, Regents of the University of California
88
*
9-
* $PostgreSQL: pgsql/src/include/utils/lsyscache.h,v 1.85 2003/11/29 22:41:15 pgsql Exp $
9+
* $PostgreSQL: pgsql/src/include/utils/lsyscache.h,v 1.86 2003/12/03 17:45:10 tgl Exp $
1010
*
1111
*-------------------------------------------------------------------------
1212
*/
@@ -42,6 +42,7 @@ extern bool opclass_is_btree(Oid opclass);
4242
externboolopclass_is_hash(Oidopclass);
4343
externRegProcedureget_opcode(Oidopno);
4444
externchar*get_opname(Oidopno);
45+
externvoidop_input_types(Oidopno,Oid*lefttype,Oid*righttype);
4546
externboolop_mergejoinable(Oidopno,Oid*leftOp,Oid*rightOp);
4647
externvoidop_mergejoin_crossops(Oidopno,Oid*ltop,Oid*gtop,
4748
RegProcedure*ltproc,RegProcedure*gtproc);

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp