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

Commit08ccdf0

Browse files
committed
Fix oversight in planning for multiple indexscans driven by
ScalarArrayOpExpr index quals: we were estimating the right totalnumber of rows returned, but treating the index-access part of thecost as if a single scan were fetching that many consecutive indextuples. Actually we should treat it as a multiple indexscan, andif there are enough of 'em the Mackert-Lohman discount should kick in.
1 parentcffd89c commit08ccdf0

File tree

3 files changed

+84
-39
lines changed

3 files changed

+84
-39
lines changed

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

Lines changed: 1 addition & 30 deletions
Original file line numberDiff line numberDiff line change
@@ -54,7 +54,7 @@
5454
* Portions Copyright (c) 1994, Regents of the University of California
5555
*
5656
* IDENTIFICATION
57-
* $PostgreSQL: pgsql/src/backend/optimizer/path/costsize.c,v 1.159 2006/07/0118:38:32 tgl Exp $
57+
* $PostgreSQL: pgsql/src/backend/optimizer/path/costsize.c,v 1.160 2006/07/0122:07:23 tgl Exp $
5858
*
5959
*-------------------------------------------------------------------------
6060
*/
@@ -112,7 +112,6 @@ boolenable_hashjoin = true;
112112

113113

114114
staticboolcost_qual_eval_walker(Node*node,QualCost*total);
115-
staticintestimate_array_length(Node*arrayexpr);
116115
staticSelectivityapprox_selectivity(PlannerInfo*root,List*quals,
117116
JoinTypejointype);
118117
staticSelectivityjoin_in_selectivity(JoinPath*path,PlannerInfo*root);
@@ -691,34 +690,6 @@ cost_tidscan(Path *path, PlannerInfo *root,
691690
path->total_cost=startup_cost+run_cost;
692691
}
693692

694-
/*
695-
* Estimate number of elements in the array yielded by an expression.
696-
*/
697-
staticint
698-
estimate_array_length(Node*arrayexpr)
699-
{
700-
if (arrayexpr&&IsA(arrayexpr,Const))
701-
{
702-
Datumarraydatum= ((Const*)arrayexpr)->constvalue;
703-
boolarrayisnull= ((Const*)arrayexpr)->constisnull;
704-
ArrayType*arrayval;
705-
706-
if (arrayisnull)
707-
return0;
708-
arrayval=DatumGetArrayTypeP(arraydatum);
709-
returnArrayGetNItems(ARR_NDIM(arrayval),ARR_DIMS(arrayval));
710-
}
711-
elseif (arrayexpr&&IsA(arrayexpr,ArrayExpr))
712-
{
713-
returnlist_length(((ArrayExpr*)arrayexpr)->elements);
714-
}
715-
else
716-
{
717-
/* default guess */
718-
return10;
719-
}
720-
}
721-
722693
/*
723694
* cost_subqueryscan
724695
* Determines and returns the cost of scanning a subquery RTE.

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

Lines changed: 81 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -15,7 +15,7 @@
1515
*
1616
*
1717
* IDENTIFICATION
18-
* $PostgreSQL: pgsql/src/backend/utils/adt/selfuncs.c,v 1.207 2006/06/06 17:59:57 tgl Exp $
18+
* $PostgreSQL: pgsql/src/backend/utils/adt/selfuncs.c,v 1.208 2006/07/01 22:07:23 tgl Exp $
1919
*
2020
*-------------------------------------------------------------------------
2121
*/
@@ -1394,7 +1394,6 @@ scalararraysel(PlannerInfo *root,
13941394
{
13951395
oprsel=get_oprjoin(operator);
13961396
selarg4=Int16GetDatum(jointype);
1397-
13981397
}
13991398
else
14001399
{
@@ -1519,6 +1518,7 @@ scalararraysel(PlannerInfo *root,
15191518
s1=useOr ?0.0 :1.0;
15201519
/*
15211520
* Arbitrarily assume 10 elements in the eventual array value
1521+
* (see also estimate_array_length)
15221522
*/
15231523
for (i=0;i<10;i++)
15241524
{
@@ -1535,6 +1535,37 @@ scalararraysel(PlannerInfo *root,
15351535
returns1;
15361536
}
15371537

1538+
/*
1539+
* Estimate number of elements in the array yielded by an expression.
1540+
*
1541+
* It's important that this agree with scalararraysel.
1542+
*/
1543+
int
1544+
estimate_array_length(Node*arrayexpr)
1545+
{
1546+
if (arrayexpr&&IsA(arrayexpr,Const))
1547+
{
1548+
Datumarraydatum= ((Const*)arrayexpr)->constvalue;
1549+
boolarrayisnull= ((Const*)arrayexpr)->constisnull;
1550+
ArrayType*arrayval;
1551+
1552+
if (arrayisnull)
1553+
return0;
1554+
arrayval=DatumGetArrayTypeP(arraydatum);
1555+
returnArrayGetNItems(ARR_NDIM(arrayval),ARR_DIMS(arrayval));
1556+
}
1557+
elseif (arrayexpr&&IsA(arrayexpr,ArrayExpr)&&
1558+
!((ArrayExpr*)arrayexpr)->multidims)
1559+
{
1560+
returnlist_length(((ArrayExpr*)arrayexpr)->elements);
1561+
}
1562+
else
1563+
{
1564+
/* default guess --- see also scalararraysel */
1565+
return10;
1566+
}
1567+
}
1568+
15381569
/*
15391570
*rowcomparesel- Selectivity of RowCompareExpr Node.
15401571
*
@@ -4473,10 +4504,14 @@ genericcostestimate(PlannerInfo *root,
44734504
double*indexCorrelation)
44744505
{
44754506
doublenumIndexPages;
4507+
doublenum_sa_scans;
4508+
doublenum_outer_scans;
4509+
doublenum_scans;
44764510
QualCostindex_qual_cost;
44774511
doublequal_op_cost;
44784512
doublequal_arg_cost;
44794513
List*selectivityQuals;
4514+
ListCell*l;
44804515

44814516
/*
44824517
* If the index is partial, AND the index predicate with the explicitly
@@ -4513,6 +4548,25 @@ genericcostestimate(PlannerInfo *root,
45134548
else
45144549
selectivityQuals=indexQuals;
45154550

4551+
/*
4552+
* Check for ScalarArrayOpExpr index quals, and estimate the number
4553+
* of index scans that will be performed.
4554+
*/
4555+
num_sa_scans=1;
4556+
foreach(l,indexQuals)
4557+
{
4558+
RestrictInfo*rinfo= (RestrictInfo*)lfirst(l);
4559+
4560+
if (IsA(rinfo->clause,ScalarArrayOpExpr))
4561+
{
4562+
ScalarArrayOpExpr*saop= (ScalarArrayOpExpr*)rinfo->clause;
4563+
intalength=estimate_array_length(lsecond(saop->args));
4564+
4565+
if (alength>1)
4566+
num_sa_scans *=alength;
4567+
}
4568+
}
4569+
45164570
/* Estimate the fraction of main-table tuples that will be visited */
45174571
*indexSelectivity=clauselist_selectivity(root,selectivityQuals,
45184572
index->rel->relid,
@@ -4527,8 +4581,15 @@ genericcostestimate(PlannerInfo *root,
45274581
numIndexTuples=*indexSelectivity*index->rel->tuples;
45284582

45294583
/*
4530-
* We can bound the number of tuples by the index size in any case. Also,
4531-
* always estimate at least one tuple is touched, even when
4584+
* The estimate obtained so far counts all the tuples returned by all
4585+
* scans of ScalarArrayOpExpr calls. We want to consider the per-scan
4586+
* number, so adjust. This is a handy place to round to integer, too.
4587+
*/
4588+
numIndexTuples=rint(numIndexTuples /num_sa_scans);
4589+
4590+
/*
4591+
* We can bound the number of tuples by the index size in any case.
4592+
* Also, always estimate at least one tuple is touched, even when
45324593
* indexSelectivity estimate is tiny.
45334594
*/
45344595
if (numIndexTuples>index->tuples)
@@ -4556,7 +4617,8 @@ genericcostestimate(PlannerInfo *root,
45564617
*
45574618
* The above calculations are all per-index-scan. However, if we are
45584619
* in a nestloop inner scan, we can expect the scan to be repeated (with
4559-
* different search keys) for each row of the outer relation. This
4620+
* different search keys) for each row of the outer relation. Likewise,
4621+
* ScalarArrayOpExpr quals result in multiple index scans. This
45604622
* creates the potential for cache effects to reduce the number of
45614623
* disk page fetches needed. We want to estimate the average per-scan
45624624
* I/O cost in the presence of caching.
@@ -4569,7 +4631,17 @@ genericcostestimate(PlannerInfo *root,
45694631
*/
45704632
if (outer_rel!=NULL&&outer_rel->rows>1)
45714633
{
4572-
doublenum_scans=outer_rel->rows;
4634+
num_outer_scans=outer_rel->rows;
4635+
num_scans=num_sa_scans*num_outer_scans;
4636+
}
4637+
else
4638+
{
4639+
num_outer_scans=1;
4640+
num_scans=num_sa_scans;
4641+
}
4642+
4643+
if (num_scans>1)
4644+
{
45734645
doublepages_fetched;
45744646

45754647
/* total page fetches ignoring cache effects */
@@ -4582,9 +4654,10 @@ genericcostestimate(PlannerInfo *root,
45824654

45834655
/*
45844656
* Now compute the total disk access cost, and then report a
4585-
* pro-rated share for one index scan.
4657+
* pro-rated share for each outer scan. (Don't pro-rate for
4658+
* ScalarArrayOpExpr, since that's internal to the indexscan.)
45864659
*/
4587-
*indexTotalCost= (pages_fetched*random_page_cost) /num_scans;
4660+
*indexTotalCost= (pages_fetched*random_page_cost) /num_outer_scans;
45884661
}
45894662
else
45904663
{

‎src/include/utils/selfuncs.h

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
* Portions Copyright (c) 1996-2006, PostgreSQL Global Development Group
99
* Portions Copyright (c) 1994, Regents of the University of California
1010
*
11-
* $PostgreSQL: pgsql/src/include/utils/selfuncs.h,v 1.33 2006/05/02 11:28:55 teodor Exp $
11+
* $PostgreSQL: pgsql/src/include/utils/selfuncs.h,v 1.34 2006/07/01 22:07:23 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -151,6 +151,7 @@ extern Selectivity scalararraysel(PlannerInfo *root,
151151
ScalarArrayOpExpr*clause,
152152
boolis_join_clause,
153153
intvarRelid,JoinTypejointype);
154+
externintestimate_array_length(Node*arrayexpr);
154155
externSelectivityrowcomparesel(PlannerInfo*root,
155156
RowCompareExpr*clause,
156157
intvarRelid,JoinTypejointype);

0 commit comments

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