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

Commit0f61d4d

Browse files
committed
Improve relation width estimation for subqueries.
As per the ancient comment for set_rel_width, it really wasn't much goodfor relations that aren't plain tables: it would never find any stats andwould always fall back on datatype-based estimates, which are often prettysilly. Fix that by copying up width estimates from the subquery planningprocess.At some point we might want to do this for CTEs too, but that would be asignificantly more invasive patch because the sub-PlannerInfo is no longeraccessible by the time it's needed. I refrained from doing anything aboutthat, partly for fear of breaking the unmerged CTE-related patches.In passing, also generate less bogus width estimates for whole-row Vars.Per a gripe from Jon Nelson.
1 parentfe24d78 commit0f61d4d

File tree

6 files changed

+150
-20
lines changed

6 files changed

+150
-20
lines changed

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

Lines changed: 1 addition & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -758,11 +758,8 @@ set_subquery_pathlist(PlannerInfo *root, RelOptInfo *rel,
758758
rel->subrtable=subroot->parse->rtable;
759759
rel->subrowmark=subroot->rowMarks;
760760

761-
/* Copy number of output rows from subplan */
762-
rel->tuples=rel->subplan->plan_rows;
763-
764761
/* Mark rel with estimated output rows, width, etc */
765-
set_baserel_size_estimates(root,rel);
762+
set_subquery_size_estimates(root,rel,subroot);
766763

767764
/* Convert subquery pathkeys to outer representation */
768765
pathkeys=convert_subquery_pathkeys(root,rel,subroot->query_pathkeys);

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

Lines changed: 129 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -76,6 +76,7 @@
7676
#include"optimizer/cost.h"
7777
#include"optimizer/pathnode.h"
7878
#include"optimizer/placeholder.h"
79+
#include"optimizer/plancat.h"
7980
#include"optimizer/planmain.h"
8081
#include"optimizer/restrictinfo.h"
8182
#include"parser/parsetree.h"
@@ -2986,7 +2987,7 @@ approx_tuple_count(PlannerInfo *root, JoinPath *path, List *quals)
29862987
*Set the size estimates for the given base relation.
29872988
*
29882989
* The rel's targetlist and restrictinfo list must have been constructed
2989-
* already.
2990+
* already, and rel->tuples must be set.
29902991
*
29912992
* We set the following fields of the rel node:
29922993
*rows: the estimated number of output tuples (after applying
@@ -3151,6 +3152,76 @@ set_joinrel_size_estimates(PlannerInfo *root, RelOptInfo *rel,
31513152
rel->rows=clamp_row_est(nrows);
31523153
}
31533154

3155+
/*
3156+
* set_subquery_size_estimates
3157+
*Set the size estimates for a base relation that is a subquery.
3158+
*
3159+
* The rel's targetlist and restrictinfo list must have been constructed
3160+
* already, and the plan for the subquery must have been completed.
3161+
* We look at the subquery's plan and PlannerInfo to extract data.
3162+
*
3163+
* We set the same fields as set_baserel_size_estimates.
3164+
*/
3165+
void
3166+
set_subquery_size_estimates(PlannerInfo*root,RelOptInfo*rel,
3167+
PlannerInfo*subroot)
3168+
{
3169+
RangeTblEntry*rte;
3170+
ListCell*lc;
3171+
3172+
/* Should only be applied to base relations that are subqueries */
3173+
Assert(rel->relid>0);
3174+
rte=planner_rt_fetch(rel->relid,root);
3175+
Assert(rte->rtekind==RTE_SUBQUERY);
3176+
3177+
/* Copy raw number of output rows from subplan */
3178+
rel->tuples=rel->subplan->plan_rows;
3179+
3180+
/*
3181+
* Compute per-output-column width estimates by examining the subquery's
3182+
* targetlist. For any output that is a plain Var, get the width estimate
3183+
* that was made while planning the subquery. Otherwise, fall back on a
3184+
* datatype-based estimate.
3185+
*/
3186+
foreach(lc,subroot->parse->targetList)
3187+
{
3188+
TargetEntry*te= (TargetEntry*)lfirst(lc);
3189+
Node*texpr= (Node*)te->expr;
3190+
int32item_width;
3191+
3192+
Assert(IsA(te,TargetEntry));
3193+
/* junk columns aren't visible to upper query */
3194+
if (te->resjunk)
3195+
continue;
3196+
3197+
/*
3198+
* XXX This currently doesn't work for subqueries containing set
3199+
* operations, because the Vars in their tlists are bogus references
3200+
* to the first leaf subquery, which wouldn't give the right answer
3201+
* even if we could still get to its PlannerInfo. So fall back on
3202+
* datatype in that case.
3203+
*/
3204+
if (IsA(texpr,Var)&&
3205+
subroot->parse->setOperations==NULL)
3206+
{
3207+
Var*var= (Var*)texpr;
3208+
RelOptInfo*subrel=find_base_rel(subroot,var->varno);
3209+
3210+
item_width=subrel->attr_widths[var->varattno-subrel->min_attr];
3211+
}
3212+
else
3213+
{
3214+
item_width=get_typavgwidth(exprType(texpr),exprTypmod(texpr));
3215+
}
3216+
Assert(item_width>0);
3217+
Assert(te->resno >=rel->min_attr&&te->resno <=rel->max_attr);
3218+
rel->attr_widths[te->resno-rel->min_attr]=item_width;
3219+
}
3220+
3221+
/* Now estimate number of output rows, etc */
3222+
set_baserel_size_estimates(root,rel);
3223+
}
3224+
31543225
/*
31553226
* set_function_size_estimates
31563227
*Set the size estimates for a base relation that is a function call.
@@ -3251,11 +3322,17 @@ set_cte_size_estimates(PlannerInfo *root, RelOptInfo *rel, Plan *cteplan)
32513322
* set_rel_width
32523323
*Set the estimated output width of a base relation.
32533324
*
3325+
* The estimated output width is the sum of the per-attribute width estimates
3326+
* for the actually-referenced columns, plus any PHVs or other expressions
3327+
* that have to be calculated at this relation. This is the amount of data
3328+
* we'd need to pass upwards in case of a sort, hash, etc.
3329+
*
32543330
* NB: this works best on plain relations because it prefers to look at
3255-
* real Vars. It will fail to make use of pg_statistic info when applied
3256-
* to a subquery relation, even if the subquery outputs are simple vars
3257-
* that we could have gotten info for.Is it worth trying to be smarter
3258-
* about subqueries?
3331+
* real Vars. For subqueries, set_subquery_size_estimates will already have
3332+
* copied up whatever per-column estimates were made within the subquery,
3333+
* and for other types of rels there isn't much we can do anyway. We fall
3334+
* back on (fairly stupid) datatype-based width estimates if we can't get
3335+
* any better number.
32593336
*
32603337
* The per-attribute width estimates are cached for possible re-use while
32613338
* building join relations.
@@ -3265,6 +3342,7 @@ set_rel_width(PlannerInfo *root, RelOptInfo *rel)
32653342
{
32663343
Oidreloid=planner_rt_fetch(rel->relid,root)->relid;
32673344
int32tuple_width=0;
3345+
boolhave_wholerow_var= false;
32683346
ListCell*lc;
32693347

32703348
foreach(lc,rel->reltargetlist)
@@ -3284,8 +3362,18 @@ set_rel_width(PlannerInfo *root, RelOptInfo *rel)
32843362
ndx=var->varattno-rel->min_attr;
32853363

32863364
/*
3287-
* The width probably hasn't been cached yet, but may as well
3288-
* check
3365+
* If it's a whole-row Var, we'll deal with it below after we
3366+
* have already cached as many attr widths as possible.
3367+
*/
3368+
if (var->varattno==0)
3369+
{
3370+
have_wholerow_var= true;
3371+
continue;
3372+
}
3373+
3374+
/*
3375+
* The width may have been cached already (especially if it's
3376+
* a subquery), so don't duplicate effort.
32893377
*/
32903378
if (rel->attr_widths[ndx]>0)
32913379
{
@@ -3294,7 +3382,7 @@ set_rel_width(PlannerInfo *root, RelOptInfo *rel)
32943382
}
32953383

32963384
/* Try to get column width from statistics */
3297-
if (reloid!=InvalidOid)
3385+
if (reloid!=InvalidOid&&var->varattno>0)
32983386
{
32993387
item_width=get_attavgwidth(reloid,var->varattno);
33003388
if (item_width>0)
@@ -3335,6 +3423,39 @@ set_rel_width(PlannerInfo *root, RelOptInfo *rel)
33353423
tuple_width+=item_width;
33363424
}
33373425
}
3426+
3427+
/*
3428+
* If we have a whole-row reference, estimate its width as the sum of
3429+
* per-column widths plus sizeof(HeapTupleHeaderData).
3430+
*/
3431+
if (have_wholerow_var)
3432+
{
3433+
int32wholerow_width=sizeof(HeapTupleHeaderData);
3434+
3435+
if (reloid!=InvalidOid)
3436+
{
3437+
/* Real relation, so estimate true tuple width */
3438+
wholerow_width+=get_relation_data_width(reloid,
3439+
rel->attr_widths-rel->min_attr);
3440+
}
3441+
else
3442+
{
3443+
/* Do what we can with info for a phony rel */
3444+
AttrNumberi;
3445+
3446+
for (i=1;i <=rel->max_attr;i++)
3447+
wholerow_width+=rel->attr_widths[i-rel->min_attr];
3448+
}
3449+
3450+
rel->attr_widths[0-rel->min_attr]=wholerow_width;
3451+
3452+
/*
3453+
* Include the whole-row Var as part of the output tuple. Yes,
3454+
* that really is what happens at runtime.
3455+
*/
3456+
tuple_width+=wholerow_width;
3457+
}
3458+
33383459
Assert(tuple_width >=0);
33393460
rel->width=tuple_width;
33403461
}

‎src/backend/optimizer/plan/planner.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -3102,7 +3102,7 @@ plan_cluster_use_sort(Oid tableOid, Oid indexOid)
31023102
* set_baserel_size_estimates, just do a quick hack for rows and width.
31033103
*/
31043104
rel->rows=rel->tuples;
3105-
rel->width=get_relation_data_width(tableOid);
3105+
rel->width=get_relation_data_width(tableOid,NULL);
31063106

31073107
root->total_table_pages=rel->pages;
31083108

‎src/backend/optimizer/util/plancat.c

Lines changed: 16 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -322,7 +322,7 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent,
322322
* estimate_rel_size - estimate # pages and # tuples in a table or index
323323
*
324324
* If attr_widths isn't NULL, it points to the zero-index entry of the
325-
* relation'sattr_width[] cache; we fill this in if we have need to compute
325+
* relation'sattr_widths[] cache; we fill this in if we have need to compute
326326
* the attribute widths for estimation purposes.
327327
*/
328328
void
@@ -435,8 +435,9 @@ estimate_rel_size(Relation rel, int32 *attr_widths,
435435
* get_rel_data_width
436436
*
437437
* Estimate the average width of (the data part of) the relation's tuples.
438-
* If attr_widths isn't NULL, also store per-column width estimates into
439-
* that array.
438+
*
439+
* If attr_widths isn't NULL, it points to the zero-index entry of the
440+
* relation's attr_widths[] cache; use and update that cache as appropriate.
440441
*
441442
* Currently we ignore dropped columns. Ideally those should be included
442443
* in the result, but we haven't got any way to get info about them; and
@@ -456,6 +457,14 @@ get_rel_data_width(Relation rel, int32 *attr_widths)
456457

457458
if (att->attisdropped)
458459
continue;
460+
461+
/* use previously cached data, if any */
462+
if (attr_widths!=NULL&&attr_widths[i]>0)
463+
{
464+
tuple_width+=attr_widths[i];
465+
continue;
466+
}
467+
459468
/* This should match set_rel_width() in costsize.c */
460469
item_width=get_attavgwidth(RelationGetRelid(rel),i);
461470
if (item_width <=0)
@@ -474,18 +483,19 @@ get_rel_data_width(Relation rel, int32 *attr_widths)
474483
/*
475484
* get_relation_data_width
476485
*
477-
* External API for get_rel_data_width
486+
* External API for get_rel_data_width: same behavior except we have to
487+
* open the relcache entry.
478488
*/
479489
int32
480-
get_relation_data_width(Oidrelid)
490+
get_relation_data_width(Oidrelid,int32*attr_widths)
481491
{
482492
int32result;
483493
Relationrelation;
484494

485495
/* As above, assume relation is already locked */
486496
relation=heap_open(relid,NoLock);
487497

488-
result=get_rel_data_width(relation,NULL);
498+
result=get_rel_data_width(relation,attr_widths);
489499

490500
heap_close(relation,NoLock);
491501

‎src/include/optimizer/cost.h

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -121,6 +121,8 @@ extern void set_joinrel_size_estimates(PlannerInfo *root, RelOptInfo *rel,
121121
RelOptInfo*inner_rel,
122122
SpecialJoinInfo*sjinfo,
123123
List*restrictlist);
124+
externvoidset_subquery_size_estimates(PlannerInfo*root,RelOptInfo*rel,
125+
PlannerInfo*subroot);
124126
externvoidset_function_size_estimates(PlannerInfo*root,RelOptInfo*rel);
125127
externvoidset_values_size_estimates(PlannerInfo*root,RelOptInfo*rel);
126128
externvoidset_cte_size_estimates(PlannerInfo*root,RelOptInfo*rel,

‎src/include/optimizer/plancat.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -31,7 +31,7 @@ extern void get_relation_info(PlannerInfo *root, Oid relationObjectId,
3131
externvoidestimate_rel_size(Relationrel,int32*attr_widths,
3232
BlockNumber*pages,double*tuples);
3333

34-
externint32get_relation_data_width(Oidrelid);
34+
externint32get_relation_data_width(Oidrelid,int32*attr_widths);
3535

3636
externboolrelation_excluded_by_constraints(PlannerInfo*root,
3737
RelOptInfo*rel,RangeTblEntry*rte);

0 commit comments

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