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

Commit6254465

Browse files
committed
Extend code that deduces implied equality clauses to detect whether a
clause being added to a particular restriction-clause list is redundantwith those already in the list. This avoids useless work at runtime,and (perhaps more importantly) keeps the selectivity estimation routinesfrom generating too-small estimates of numbers of output rows.Also some minor improvements in OPTIMIZER_DEBUG displays.
1 parent5045004 commit6254465

File tree

9 files changed

+373
-110
lines changed

9 files changed

+373
-110
lines changed

‎src/backend/nodes/print.c

Lines changed: 34 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $Header: /cvsroot/pgsql/src/backend/nodes/print.c,v 1.47 2001/03/22 03:59:32 momjian Exp $
11+
* $Header: /cvsroot/pgsql/src/backend/nodes/print.c,v 1.48 2001/10/18 16:11:41 tgl Exp $
1212
*
1313
* HISTORY
1414
* AUTHORDATEMAJOR EVENT
@@ -20,10 +20,12 @@
2020
#include"postgres.h"
2121

2222
#include"access/printtup.h"
23+
#include"catalog/pg_type.h"
2324
#include"nodes/print.h"
2425
#include"optimizer/clauses.h"
2526
#include"parser/parsetree.h"
2627
#include"utils/lsyscache.h"
28+
#include"utils/syscache.h"
2729

2830
staticchar*plannode_type(Plan*p);
2931

@@ -188,6 +190,36 @@ print_expr(Node *expr, List *rtable)
188190
}
189191
printf("%s.%s",relname,attname);
190192
}
193+
elseif (IsA(expr,Const))
194+
{
195+
Const*c= (Const*)expr;
196+
HeapTupletypeTup;
197+
Oidtypoutput;
198+
Oidtypelem;
199+
char*outputstr;
200+
201+
if (c->constisnull)
202+
{
203+
printf("NULL");
204+
return;
205+
}
206+
207+
typeTup=SearchSysCache(TYPEOID,
208+
ObjectIdGetDatum(c->consttype),
209+
0,0,0);
210+
if (!HeapTupleIsValid(typeTup))
211+
elog(ERROR,"Cache lookup for type %u failed",c->consttype);
212+
typoutput= ((Form_pg_type)GETSTRUCT(typeTup))->typoutput;
213+
typelem= ((Form_pg_type)GETSTRUCT(typeTup))->typelem;
214+
ReleaseSysCache(typeTup);
215+
216+
outputstr=DatumGetCString(OidFunctionCall3(typoutput,
217+
c->constvalue,
218+
ObjectIdGetDatum(typelem),
219+
Int32GetDatum(-1)));
220+
printf("%s",outputstr);
221+
pfree(outputstr);
222+
}
191223
elseif (IsA(expr,Expr))
192224
{
193225
Expr*e= (Expr*)expr;
@@ -232,7 +264,7 @@ print_pathkeys(List *pathkeys, List *rtable)
232264
if (lnext(k))
233265
printf(", ");
234266
}
235-
printf(")");
267+
printf(")");
236268
if (lnext(i))
237269
printf(", ");
238270
}

‎src/backend/optimizer/README

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -461,6 +461,23 @@ code than it would otherwise have, and reduces the number of tuples
461461
processed in join stages, so it's a win to make these deductions even
462462
if we weren't forced to.
463463

464+
When we generate implied equality constraints, we may find ourselves
465+
adding redundant clauses to specific relations. For example, consider
466+
SELECT * FROM t1, t2, t3 WHERE t1.a = t2.b AND t2.b = t3.c;
467+
We will generate the implied clause t1.a = t3.c and add it to the tree.
468+
This is good since it allows us to consider joining t1 and t3 directly,
469+
which we otherwise wouldn't do. But when we reach the stage of joining
470+
all three relations, we will have redundant join clauses --- eg, if we
471+
join t1 and t2 first, then the path that joins (t1 t2) to t3 will have
472+
both t2.b = t3.c and t1.a = t3.c as restriction clauses. This is bad;
473+
not only is evaluation of the extra clause useless work at runtime,
474+
but the selectivity estimator routines will underestimate the number
475+
of tuples produced since they won't know that the two clauses are
476+
perfectly redundant. We fix this by detecting and removing redundant
477+
clauses as the restriction clause list is built for each join. (We
478+
can't do it sooner, since which clauses are redundant will vary depending
479+
on the join order.)
480+
464481
Yet another implication of all this is that mergejoinable operators
465482
must form closed equivalence sets. For example, if "int2 = int4"
466483
and "int4 = int8" are both marked mergejoinable, then there had better

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

Lines changed: 85 additions & 69 deletions
Original file line numberDiff line numberDiff line change
@@ -8,13 +8,16 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $Header: /cvsroot/pgsql/src/backend/optimizer/path/allpaths.c,v 1.78 2001/07/31 17:56:30 tgl Exp $
11+
* $Header: /cvsroot/pgsql/src/backend/optimizer/path/allpaths.c,v 1.79 2001/10/18 16:11:41 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
1515

1616
#include"postgres.h"
1717

18+
#ifdefOPTIMIZER_DEBUG
19+
#include"nodes/print.h"
20+
#endif
1821
#include"optimizer/clauses.h"
1922
#include"optimizer/cost.h"
2023
#include"optimizer/geqo.h"
@@ -42,11 +45,6 @@ static void set_subquery_pathlist(Query *root, RelOptInfo *rel,
4245
staticRelOptInfo*make_one_rel_by_joins(Query*root,intlevels_needed,
4346
List*initial_rels);
4447

45-
#ifdefOPTIMIZER_DEBUG
46-
staticvoiddebug_print_rel(Query*root,RelOptInfo*rel);
47-
48-
#endif
49-
5048

5149
/*
5250
* make_one_rel
@@ -116,6 +114,10 @@ set_base_rel_pathlists(Query *root)
116114
/* Plain relation */
117115
set_plain_rel_pathlist(root,rel,rte);
118116
}
117+
118+
#ifdefOPTIMIZER_DEBUG
119+
debug_print_rel(root,rel);
120+
#endif
119121
}
120122
}
121123

@@ -520,32 +522,40 @@ make_one_rel_by_joins(Query *root, int levels_needed, List *initial_rels)
520522
#ifdefOPTIMIZER_DEBUG
521523

522524
staticvoid
523-
print_joinclauses(Query*root,List*clauses)
525+
print_relids(Relidsrelids)
526+
{
527+
List*l;
528+
529+
foreach(l,relids)
530+
{
531+
printf("%d",lfirsti(l));
532+
if (lnext(l))
533+
printf(" ");
534+
}
535+
}
536+
537+
staticvoid
538+
print_restrictclauses(Query*root,List*clauses)
524539
{
525540
List*l;
526-
externvoidprint_expr(Node*expr,List*rtable);/* in print.c */
527541

528542
foreach(l,clauses)
529543
{
530544
RestrictInfo*c=lfirst(l);
531545

532546
print_expr((Node*)c->clause,root->rtable);
533547
if (lnext(l))
534-
printf(" ");
548+
printf(", ");
535549
}
536550
}
537551

538552
staticvoid
539553
print_path(Query*root,Path*path,intindent)
540554
{
541-
char*ptype=NULL;
542-
JoinPath*jp;
543-
booljoin= false;
555+
constchar*ptype;
556+
booljoin;
544557
inti;
545558

546-
for (i=0;i<indent;i++)
547-
printf("\t");
548-
549559
switch (nodeTag(path))
550560
{
551561
caseT_Path:
@@ -556,6 +566,10 @@ print_path(Query *root, Path *path, int indent)
556566
ptype="IdxScan";
557567
join= false;
558568
break;
569+
caseT_TidPath:
570+
ptype="TidScan";
571+
join= false;
572+
break;
559573
caseT_NestPath:
560574
ptype="Nestloop";
561575
join= true;
@@ -569,89 +583,91 @@ print_path(Query *root, Path *path, int indent)
569583
join= true;
570584
break;
571585
default:
586+
ptype="???Path";
587+
join= false;
572588
break;
573589
}
590+
591+
for (i=0;i<indent;i++)
592+
printf("\t");
593+
printf("%s(",ptype);
594+
print_relids(path->parent->relids);
595+
printf(") rows=%.0f cost=%.2f..%.2f\n",
596+
path->parent->rows,path->startup_cost,path->total_cost);
597+
598+
if (path->pathkeys)
599+
{
600+
for (i=0;i<indent;i++)
601+
printf("\t");
602+
printf(" pathkeys: ");
603+
print_pathkeys(path->pathkeys,root->rtable);
604+
}
605+
574606
if (join)
575607
{
576-
jp= (JoinPath*)path;
608+
JoinPath*jp= (JoinPath*)path;
577609

578-
printf("%s rows=%.0f cost=%.2f..%.2f\n",
579-
ptype,path->parent->rows,
580-
path->startup_cost,path->total_cost);
610+
for (i=0;i<indent;i++)
611+
printf("\t");
612+
printf(" clauses: ");
613+
print_restrictclauses(root,jp->joinrestrictinfo);
614+
printf("\n");
581615

582-
if (path->pathkeys)
616+
if (nodeTag(path)==T_MergePath)
583617
{
584-
for (i=0;i<indent;i++)
585-
printf("\t");
586-
printf(" pathkeys=");
587-
print_pathkeys(path->pathkeys,root->rtable);
588-
}
618+
MergePath*mp= (MergePath*)path;
589619

590-
switch (nodeTag(path))
591-
{
592-
caseT_MergePath:
593-
caseT_HashPath:
620+
if (mp->outersortkeys||mp->innersortkeys)
621+
{
594622
for (i=0;i<indent;i++)
595623
printf("\t");
596-
printf(" clauses=(");
597-
print_joinclauses(root,jp->joinrestrictinfo);
598-
printf(")\n");
599-
600-
if (nodeTag(path)==T_MergePath)
601-
{
602-
MergePath*mp= (MergePath*)path;
603-
604-
if (mp->outersortkeys||mp->innersortkeys)
605-
{
606-
for (i=0;i<indent;i++)
607-
printf("\t");
608-
printf(" sortouter=%d sortinner=%d\n",
609-
((mp->outersortkeys) ?1 :0),
610-
((mp->innersortkeys) ?1 :0));
611-
}
612-
}
613-
break;
614-
default:
615-
break;
624+
printf(" sortouter=%d sortinner=%d\n",
625+
((mp->outersortkeys) ?1 :0),
626+
((mp->innersortkeys) ?1 :0));
627+
}
616628
}
629+
617630
print_path(root,jp->outerjoinpath,indent+1);
618631
print_path(root,jp->innerjoinpath,indent+1);
619632
}
620-
else
621-
{
622-
intrelid=lfirsti(path->parent->relids);
623-
624-
printf("%s(%d) rows=%.0f cost=%.2f..%.2f\n",
625-
ptype,relid,path->parent->rows,
626-
path->startup_cost,path->total_cost);
627-
628-
if (path->pathkeys)
629-
{
630-
for (i=0;i<indent;i++)
631-
printf("\t");
632-
printf(" pathkeys=");
633-
print_pathkeys(path->pathkeys,root->rtable);
634-
}
635-
}
636633
}
637634

638-
staticvoid
635+
void
639636
debug_print_rel(Query*root,RelOptInfo*rel)
640637
{
641638
List*l;
642639

643-
printf("(");
644-
foreach(l,rel->relids)
645-
printf("%d ",lfirsti(l));
640+
printf("RELOPTINFO (");
641+
print_relids(rel->relids);
646642
printf("): rows=%.0f width=%d\n",rel->rows,rel->width);
647643

644+
if (rel->baserestrictinfo)
645+
{
646+
printf("\tbaserestrictinfo: ");
647+
print_restrictclauses(root,rel->baserestrictinfo);
648+
printf("\n");
649+
}
650+
651+
foreach(l,rel->joininfo)
652+
{
653+
JoinInfo*j= (JoinInfo*)lfirst(l);
654+
655+
printf("\tjoininfo (");
656+
print_relids(j->unjoined_relids);
657+
printf("): ");
658+
print_restrictclauses(root,j->jinfo_restrictinfo);
659+
printf("\n");
660+
}
661+
648662
printf("\tpath list:\n");
649663
foreach(l,rel->pathlist)
650664
print_path(root,lfirst(l),1);
651665
printf("\n\tcheapest startup path:\n");
652666
print_path(root,rel->cheapest_startup_path,1);
653667
printf("\n\tcheapest total path:\n");
654668
print_path(root,rel->cheapest_total_path,1);
669+
printf("\n");
670+
fflush(stdout);
655671
}
656672

657673
#endif/* OPTIMIZER_DEBUG */

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

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $Header: /cvsroot/pgsql/src/backend/optimizer/path/joinrels.c,v 1.53 2001/05/20 20:28:18 tgl Exp $
11+
* $Header: /cvsroot/pgsql/src/backend/optimizer/path/joinrels.c,v 1.54 2001/10/18 16:11:41 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -374,6 +374,10 @@ make_jointree_rel(Query *root, Node *jtnode)
374374
*/
375375
set_cheapest(rel);
376376

377+
#ifdefOPTIMIZER_DEBUG
378+
debug_print_rel(root,rel);
379+
#endif
380+
377381
returnrel;
378382
}
379383
else

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

Lines changed: 2 additions & 2 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-
* $Header: /cvsroot/pgsql/src/backend/optimizer/path/pathkeys.c,v 1.32 2001/03/22 06:16:14 momjian Exp $
14+
* $Header: /cvsroot/pgsql/src/backend/optimizer/path/pathkeys.c,v 1.33 2001/10/1816:11:41 tgl Exp $
1515
*
1616
*-------------------------------------------------------------------------
1717
*/
@@ -705,7 +705,7 @@ make_pathkeys_for_sortclauses(List *sortclauses,
705705
* This is a worthwhile savings because these routines will be invoked
706706
* many times when dealing with a many-relation query.
707707
*/
708-
staticvoid
708+
void
709709
cache_mergeclause_pathkeys(Query*root,RestrictInfo*restrictinfo)
710710
{
711711
Node*key;

0 commit comments

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