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

Commit8449df8

Browse files
committed
First cut at unifying regular selectivity estimation with indexscan
selectivity estimation wasn't right. This is better...
1 parent49581f9 commit8449df8

File tree

5 files changed

+136
-72
lines changed

5 files changed

+136
-72
lines changed

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

Lines changed: 99 additions & 54 deletions
Original file line numberDiff line numberDiff line change
@@ -1,13 +1,13 @@
11
/*-------------------------------------------------------------------------
22
*
33
* clausesel.c
4-
* Routines to computeand setclause selectivities
4+
* Routines to compute clause selectivities
55
*
66
* Copyright (c) 1994, Regents of the University of California
77
*
88
*
99
* IDENTIFICATION
10-
* $Header: /cvsroot/pgsql/src/backend/optimizer/path/clausesel.c,v 1.27 2000/01/09 00:26:31 tgl Exp $
10+
* $Header: /cvsroot/pgsql/src/backend/optimizer/path/clausesel.c,v 1.28 2000/01/23 02:06:58 tgl Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -28,52 +28,76 @@
2828
****************************************************************************/
2929

3030
/*
31-
*restrictlist_selec -
31+
*restrictlist_selectivity -
3232
* Compute the selectivity of an implicitly-ANDed list of RestrictInfo
3333
* clauses.
3434
*
35-
* This is the same as clauselist_selec except for the form of the input.
35+
* This is the same as clauselist_selectivity except for the representation
36+
* of the clause list.
3637
*/
3738
Selectivity
38-
restrictlist_selec(Query*root,List*restrictinfo_list)
39+
restrictlist_selectivity(Query*root,
40+
List*restrictinfo_list,
41+
intvarRelid)
3942
{
4043
List*clauselist=get_actual_clauses(restrictinfo_list);
4144
Selectivityresult;
4245

43-
result=clauselist_selec(root,clauselist);
46+
result=clauselist_selectivity(root,clauselist,varRelid);
4447
freeList(clauselist);
4548
returnresult;
4649
}
4750

4851
/*
49-
*clauselist_selec -
52+
*clauselist_selectivity -
5053
* Compute the selectivity of an implicitly-ANDed list of boolean
51-
* expression clauses.
54+
* expression clauses. The list can be empty, in which case 1.0
55+
* must be returned.
56+
*
57+
* See clause_selectivity() for the meaning of the varRelid parameter.
5258
*/
5359
Selectivity
54-
clauselist_selec(Query*root,List*clauses)
60+
clauselist_selectivity(Query*root,
61+
List*clauses,
62+
intvarRelid)
5563
{
5664
Selectivitys1=1.0;
5765
List*clause;
5866

5967
/* Use the product of the selectivities of the subclauses.
60-
* XXX this isprobablytoo optimistic, since the subclauses
68+
* XXX this is too optimistic, since the subclauses
6169
* are very likely not independent...
6270
*/
6371
foreach(clause,clauses)
6472
{
65-
Selectivitys2=compute_clause_selec(root, (Node*)lfirst(clause));
73+
Selectivitys2=clause_selectivity(root,
74+
(Node*)lfirst(clause),
75+
varRelid);
6676
s1=s1*s2;
6777
}
6878
returns1;
6979
}
7080

7181
/*
72-
*compute_clause_selec -
82+
*clause_selectivity -
7383
* Compute the selectivity of a general boolean expression clause.
84+
*
85+
* varRelid is either 0 or a rangetable index.
86+
*
87+
* When varRelid is not 0, only variables belonging to that relation are
88+
* considered in computing selectivity; other vars are treated as constants
89+
* of unknown values. This is appropriate for estimating the selectivity of
90+
* a join clause that is being used as a restriction clause in a scan of a
91+
* nestloop join's inner relation --- varRelid should then be the ID of the
92+
* inner relation.
93+
*
94+
* When varRelid is 0, all variables are treated as variables. This
95+
* is appropriate for ordinary join clauses and restriction clauses.
7496
*/
7597
Selectivity
76-
compute_clause_selec(Query*root,Node*clause)
98+
clause_selectivity(Query*root,
99+
Node*clause,
100+
intvarRelid)
77101
{
78102
Selectivitys1=1.0;/* default for any unhandled clause type */
79103

@@ -88,13 +112,16 @@ compute_clause_selec(Query *root, Node *clause)
88112
* didn't want to have to do system cache look ups to find out all
89113
* of that info.
90114
*/
91-
s1=restriction_selectivity(F_EQSEL,
92-
BooleanEqualOperator,
93-
getrelid(((Var*)clause)->varno,
94-
root->rtable),
95-
((Var*)clause)->varattno,
96-
Int8GetDatum(true),
97-
SEL_CONSTANT |SEL_RIGHT);
115+
Indexvarno= ((Var*)clause)->varno;
116+
117+
if (varRelid==0||varRelid==varno)
118+
s1=restriction_selectivity(F_EQSEL,
119+
BooleanEqualOperator,
120+
getrelid(varno,root->rtable),
121+
((Var*)clause)->varattno,
122+
Int8GetDatum(true),
123+
SEL_CONSTANT |SEL_RIGHT);
124+
/* an outer-relation bool var is taken as always true... */
98125
}
99126
elseif (IsA(clause,Param))
100127
{
@@ -109,12 +136,16 @@ compute_clause_selec(Query *root, Node *clause)
109136
elseif (not_clause(clause))
110137
{
111138
/* inverse of the selectivity of the underlying clause */
112-
s1=1.0-compute_clause_selec(root,
113-
(Node*)get_notclausearg((Expr*)clause));
139+
s1=1.0-clause_selectivity(root,
140+
(Node*)get_notclausearg((Expr*)clause),
141+
varRelid);
114142
}
115143
elseif (and_clause(clause))
116144
{
117-
s1=clauselist_selec(root, ((Expr*)clause)->args);
145+
/* share code with clauselist_selectivity() */
146+
s1=clauselist_selectivity(root,
147+
((Expr*)clause)->args,
148+
varRelid);
118149
}
119150
elseif (or_clause(clause))
120151
{
@@ -127,50 +158,37 @@ compute_clause_selec(Query *root, Node *clause)
127158
s1=0.0;
128159
foreach(arg, ((Expr*)clause)->args)
129160
{
130-
Selectivitys2=compute_clause_selec(root, (Node*)lfirst(arg));
161+
Selectivitys2=clause_selectivity(root,
162+
(Node*)lfirst(arg),
163+
varRelid);
131164
s1=s1+s2-s1*s2;
132165
}
133166
}
134167
elseif (is_opclause(clause))
135168
{
136-
if (NumRelids(clause)==1)
137-
{
138-
/* The opclause is not a join clause, since there is only one
139-
* relid in the clause. The clause selectivity will be based on
140-
* the operator selectivity and operand values.
141-
*/
142-
Oidopno= ((Oper*) ((Expr*)clause)->oper)->opno;
143-
RegProcedureoprrest=get_oprrest(opno);
169+
Oidopno= ((Oper*) ((Expr*)clause)->oper)->opno;
170+
boolis_join_clause;
144171

172+
if (varRelid!=0)
173+
{
145174
/*
146-
* if the oprrest procedure is missing for whatever reason, use a
147-
* selectivity of 0.5
175+
* If we are considering a nestloop join then all clauses
176+
* are restriction clauses, since we are only interested in
177+
* the one relation.
148178
*/
149-
if (!oprrest)
150-
s1= (Selectivity)0.5;
151-
else
152-
{
153-
intrelidx;
154-
AttrNumberattno;
155-
Datumconstval;
156-
intflag;
157-
Oidreloid;
158-
159-
get_relattval(clause,0,&relidx,&attno,&constval,&flag);
160-
reloid=relidx ?getrelid(relidx,root->rtable) :InvalidOid;
161-
s1=restriction_selectivity(oprrest,opno,
162-
reloid,attno,
163-
constval,flag);
164-
}
179+
is_join_clause= false;
165180
}
166181
else
167182
{
168183
/*
169-
* The clause must be a join clause. The clause selectivity will
170-
* be based on the relations to be scanned and the attributes they
171-
* are to be joined on.
184+
* Otherwise, it's a join if there's more than one relation used.
172185
*/
173-
Oidopno= ((Oper*) ((Expr*)clause)->oper)->opno;
186+
is_join_clause= (NumRelids(clause)>1);
187+
}
188+
189+
if (is_join_clause)
190+
{
191+
/* Estimate selectivity for a join clause. */
174192
RegProcedureoprjoin=get_oprjoin(opno);
175193

176194
/*
@@ -196,6 +214,33 @@ compute_clause_selec(Query *root, Node *clause)
196214
reloid2,attno2);
197215
}
198216
}
217+
else
218+
{
219+
/* Estimate selectivity for a restriction clause. */
220+
RegProcedureoprrest=get_oprrest(opno);
221+
222+
/*
223+
* if the oprrest procedure is missing for whatever reason, use a
224+
* selectivity of 0.5
225+
*/
226+
if (!oprrest)
227+
s1= (Selectivity)0.5;
228+
else
229+
{
230+
intrelidx;
231+
AttrNumberattno;
232+
Datumconstval;
233+
intflag;
234+
Oidreloid;
235+
236+
get_relattval(clause,varRelid,
237+
&relidx,&attno,&constval,&flag);
238+
reloid=relidx ?getrelid(relidx,root->rtable) :InvalidOid;
239+
s1=restriction_selectivity(oprrest,opno,
240+
reloid,attno,
241+
constval,flag);
242+
}
243+
}
199244
}
200245
elseif (is_funcclause(clause))
201246
{

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

Lines changed: 9 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -18,7 +18,7 @@
1818
* Copyright (c) 1994, Regents of the University of California
1919
*
2020
* IDENTIFICATION
21-
* $Header: /cvsroot/pgsql/src/backend/optimizer/path/costsize.c,v 1.48 2000/01/22 23:50:14 tgl Exp $
21+
* $Header: /cvsroot/pgsql/src/backend/optimizer/path/costsize.c,v 1.49 2000/01/23 02:06:59 tgl Exp $
2222
*
2323
*-------------------------------------------------------------------------
2424
*/
@@ -459,7 +459,10 @@ set_rel_rows_width(Query *root, RelOptInfo *rel)
459459
/* Should only be applied to base relations */
460460
Assert(length(rel->relids)==1);
461461

462-
rel->rows=rel->tuples*restrictlist_selec(root,rel->restrictinfo);
462+
rel->rows=rel->tuples*
463+
restrictlist_selectivity(root,
464+
rel->restrictinfo,
465+
lfirsti(rel->relids));
463466
Assert(rel->rows >=0);
464467

465468
set_rel_width(root,rel);
@@ -479,8 +482,10 @@ set_joinrel_rows_width(Query *root, RelOptInfo *rel,
479482
temp=joinpath->outerjoinpath->parent->rows*
480483
joinpath->innerjoinpath->parent->rows;
481484

482-
/* apply restrictivity */
483-
temp *=restrictlist_selec(root,joinpath->path.parent->restrictinfo);
485+
/* apply join restrictivity */
486+
temp *=restrictlist_selectivity(root,
487+
joinpath->path.parent->restrictinfo,
488+
0);
484489

485490
Assert(temp >=0);
486491
rel->rows=temp;

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

Lines changed: 10 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -9,7 +9,7 @@
99
*
1010
*
1111
* IDENTIFICATION
12-
* $Header: /cvsroot/pgsql/src/backend/optimizer/plan/createplan.c,v 1.79 2000/01/15 02:59:30 petere Exp $
12+
* $Header: /cvsroot/pgsql/src/backend/optimizer/plan/createplan.c,v 1.80 2000/01/23 02:07:00 tgl Exp $
1313
*
1414
*-------------------------------------------------------------------------
1515
*/
@@ -305,6 +305,7 @@ create_indexscan_node(Query *root,
305305
List*scan_clauses)
306306
{
307307
List*indxqual=best_path->indexqual;
308+
Indexbaserelid;
308309
List*qpqual;
309310
List*fixed_indxqual;
310311
List*ixid;
@@ -314,6 +315,7 @@ create_indexscan_node(Query *root,
314315

315316
/* there should be exactly one base rel involved... */
316317
Assert(length(best_path->path.parent->relids)==1);
318+
baserelid=lfirsti(best_path->path.parent->relids);
317319

318320
/* check to see if any of the indices are lossy */
319321
foreach(ixid,best_path->indexid)
@@ -382,7 +384,9 @@ create_indexscan_node(Query *root,
382384
{
383385
/* recompute output row estimate using all available quals */
384386
plan_rows=best_path->path.parent->tuples*
385-
clauselist_selec(root,lcons(indxqual_expr,qpqual));
387+
clauselist_selectivity(root,
388+
lcons(indxqual_expr,qpqual),
389+
baserelid);
386390
}
387391

388392
if (lossy)
@@ -401,7 +405,9 @@ create_indexscan_node(Query *root,
401405
{
402406
/* recompute output row estimate using all available quals */
403407
plan_rows=best_path->path.parent->tuples*
404-
clauselist_selec(root,nconc(listCopy(indxqual_list),qpqual));
408+
clauselist_selectivity(root,
409+
nconc(listCopy(indxqual_list),qpqual),
410+
baserelid);
405411
}
406412

407413
if (lossy)
@@ -417,7 +423,7 @@ create_indexscan_node(Query *root,
417423

418424
scan_node=make_indexscan(tlist,
419425
qpqual,
420-
lfirsti(best_path->path.parent->relids),
426+
baserelid,
421427
best_path->indexid,
422428
fixed_indxqual,
423429
indxqual);

‎src/backend/utils/adt/selfuncs.c‎

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -14,7 +14,7 @@
1414
*
1515
*
1616
* IDENTIFICATION
17-
* $Header: /cvsroot/pgsql/src/backend/utils/adt/selfuncs.c,v 1.49 2000/01/22 23:50:20 tgl Exp $
17+
* $Header: /cvsroot/pgsql/src/backend/utils/adt/selfuncs.c,v 1.50 2000/01/23 02:06:56 tgl Exp $
1818
*
1919
*-------------------------------------------------------------------------
2020
*/
@@ -725,7 +725,8 @@ genericcostestimate(Query *root, RelOptInfo *rel,
725725
doublenumIndexPages;
726726

727727
/* Estimate the fraction of main-table tuples that will be visited */
728-
*indexSelectivity=clauselist_selec(root,indexQuals);
728+
*indexSelectivity=clauselist_selectivity(root,indexQuals,
729+
lfirsti(rel->relids));
729730

730731
/* Estimate the number of index tuples that will be visited */
731732
numIndexTuples=*indexSelectivity*index->tuples;

0 commit comments

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