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

Commit281f401

Browse files
committed
Fix some planner bugs exposed by reports from Arjen van der Meijden. These
are all in new-in-8.2 logic associated with indexability of ScalarArrayOpExpr(IN-clauses) or amortization of indexscan costs across repeated indexscanson the inside of a nestloop. In particular:Fix some logic errors in the estimation for multiple scans induced by aScalarArrayOpExpr indexqual.Include a small cost component in bitmap index scans to reflect the costs ofmanipulating the bitmap itself; this is mainly to prevent a bitmap scan fromappearing to have the same cost as a plain indexscan for fetching a singletuple.Also add a per-index-scan-startup CPU cost component; while prior releaseswere clearly too pessimistic about the cost of repeated indexscans, theoriginal 8.2 coding allowed the cost of an indexscan to effectively go to zeroif repeated often enough, which is overly optimistic.Pay some attention to index correlation when estimating costs for a nestloopinner indexscan: this is significant when the plan fetches multiple heaptuples per iteration, since high correlation means those tuples are probablyon the same or adjacent heap pages.
1 parent782d68e commit281f401

File tree

2 files changed

+87
-23
lines changed

2 files changed

+87
-23
lines changed

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

Lines changed: 40 additions & 14 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.169 2006/11/11 01:14:19 tgl Exp $
57+
* $PostgreSQL: pgsql/src/backend/optimizer/path/costsize.c,v 1.170 2006/12/15 18:42:26 tgl Exp $
5858
*
5959
*-------------------------------------------------------------------------
6060
*/
@@ -276,13 +276,12 @@ cost_index(IndexPath *path, PlannerInfo *root,
276276
if (outer_rel!=NULL&&outer_rel->rows>1)
277277
{
278278
/*
279-
* For repeated indexscans, scale up the number of tuples fetched in
279+
* For repeated indexscans, the appropriate estimate for the
280+
* uncorrelated case is to scale up the number of tuples fetched in
280281
* the Mackert and Lohman formula by the number of scans, so that we
281-
* estimate the number of pages fetched by all the scans. Then
282+
* estimate the number of pages fetched by all the scans; then
282283
* pro-rate the costs for one scan. In this case we assume all the
283-
* fetches are random accesses. XXX it'd be good to include
284-
* correlation in this model, but it's not clear how to do that
285-
* without double-counting cache effects.
284+
* fetches are random accesses.
286285
*/
287286
doublenum_scans=outer_rel->rows;
288287

@@ -291,7 +290,27 @@ cost_index(IndexPath *path, PlannerInfo *root,
291290
(double)index->pages,
292291
root);
293292

294-
run_cost+= (pages_fetched*random_page_cost) /num_scans;
293+
max_IO_cost= (pages_fetched*random_page_cost) /num_scans;
294+
295+
/*
296+
* In the perfectly correlated case, the number of pages touched
297+
* by each scan is selectivity * table_size, and we can use the
298+
* Mackert and Lohman formula at the page level to estimate how
299+
* much work is saved by caching across scans. We still assume
300+
* all the fetches are random, though, which is an overestimate
301+
* that's hard to correct for without double-counting the cache
302+
* effects. (But in most cases where such a plan is actually
303+
* interesting, only one page would get fetched per scan anyway,
304+
* so it shouldn't matter much.)
305+
*/
306+
pages_fetched=ceil(indexSelectivity* (double)baserel->pages);
307+
308+
pages_fetched=index_pages_fetched(pages_fetched*num_scans,
309+
baserel->pages,
310+
(double)index->pages,
311+
root);
312+
313+
min_IO_cost= (pages_fetched*random_page_cost) /num_scans;
295314
}
296315
else
297316
{
@@ -312,15 +331,15 @@ cost_index(IndexPath *path, PlannerInfo *root,
312331
min_IO_cost=random_page_cost;
313332
if (pages_fetched>1)
314333
min_IO_cost+= (pages_fetched-1)*seq_page_cost;
334+
}
315335

316-
/*
317-
* Now interpolate based on estimated index order correlation to get
318-
* total disk I/O cost for main table accesses.
319-
*/
320-
csquared=indexCorrelation*indexCorrelation;
336+
/*
337+
* Now interpolate based on estimated index order correlation to get
338+
* total disk I/O cost for main table accesses.
339+
*/
340+
csquared=indexCorrelation*indexCorrelation;
321341

322-
run_cost+=max_IO_cost+csquared* (min_IO_cost-max_IO_cost);
323-
}
342+
run_cost+=max_IO_cost+csquared* (min_IO_cost-max_IO_cost);
324343

325344
/*
326345
* Estimate CPU costs per tuple.
@@ -614,6 +633,13 @@ cost_bitmap_tree_node(Path *path, Cost *cost, Selectivity *selec)
614633
{
615634
*cost= ((IndexPath*)path)->indextotalcost;
616635
*selec= ((IndexPath*)path)->indexselectivity;
636+
/*
637+
* Charge a small amount per retrieved tuple to reflect the costs of
638+
* manipulating the bitmap. This is mostly to make sure that a bitmap
639+
* scan doesn't look to be the same cost as an indexscan to retrieve
640+
* a single tuple.
641+
*/
642+
*cost+=0.1*cpu_operator_cost* ((IndexPath*)path)->rows;
617643
}
618644
elseif (IsA(path,BitmapAndPath))
619645
{

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

Lines changed: 47 additions & 9 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.214 2006/10/04 00:29:59 momjian Exp $
18+
* $PostgreSQL: pgsql/src/backend/utils/adt/selfuncs.c,v 1.215 2006/12/15 18:42:26 tgl Exp $
1919
*
2020
*-------------------------------------------------------------------------
2121
*/
@@ -4673,14 +4673,18 @@ genericcostestimate(PlannerInfo *root,
46734673
* way in order to get the right answer for partial indexes.
46744674
*/
46754675
if (numIndexTuples <=0.0)
4676+
{
46764677
numIndexTuples=*indexSelectivity*index->rel->tuples;
46774678

4678-
/*
4679-
* The estimate obtained so far counts all the tuples returned by all
4680-
* scans of ScalarArrayOpExpr calls. We want to consider the per-scan
4681-
* number, so adjust. This is a handy place to round to integer, too.
4682-
*/
4683-
numIndexTuples=rint(numIndexTuples /num_sa_scans);
4679+
/*
4680+
* The above calculation counts all the tuples visited across all
4681+
* scans induced by ScalarArrayOpExpr nodes. We want to consider the
4682+
* average per-indexscan number, so adjust. This is a handy place to
4683+
* round to integer, too. (If caller supplied tuple estimate, it's
4684+
* responsible for handling these considerations.)
4685+
*/
4686+
numIndexTuples=rint(numIndexTuples /num_sa_scans);
4687+
}
46844688

46854689
/*
46864690
* We can bound the number of tuples by the index size in any case. Also,
@@ -4786,7 +4790,9 @@ genericcostestimate(PlannerInfo *root,
47864790
* evaluated once at the start of the scan to reduce them to runtime keys
47874791
* to pass to the index AM (see nodeIndexscan.c). We model the per-tuple
47884792
* CPU costs as cpu_index_tuple_cost plus one cpu_operator_cost per
4789-
* indexqual operator.
4793+
* indexqual operator. Because we have numIndexTuples as a per-scan
4794+
* number, we have to multiply by num_sa_scans to get the correct result
4795+
* for ScalarArrayOpExpr cases.
47904796
*
47914797
* Note: this neglects the possible costs of rechecking lossy operators
47924798
* and OR-clause expressions. Detecting that that might be needed seems
@@ -4801,7 +4807,22 @@ genericcostestimate(PlannerInfo *root,
48014807
qual_arg_cost=0;
48024808
*indexStartupCost=qual_arg_cost;
48034809
*indexTotalCost+=qual_arg_cost;
4804-
*indexTotalCost+=numIndexTuples* (cpu_index_tuple_cost+qual_op_cost);
4810+
*indexTotalCost+=numIndexTuples*num_sa_scans* (cpu_index_tuple_cost+qual_op_cost);
4811+
4812+
/*
4813+
* We also add a CPU-cost component to represent the general costs of
4814+
* starting an indexscan, such as analysis of btree index keys and
4815+
* initial tree descent. This is estimated at 100x cpu_operator_cost,
4816+
* which is a bit arbitrary but seems the right order of magnitude.
4817+
* (As noted above, we don't charge any I/O for touching upper tree
4818+
* levels, but charging nothing at all has been found too optimistic.)
4819+
*
4820+
* Although this is startup cost with respect to any one scan, we add
4821+
* it to the "total" cost component because it's only very interesting
4822+
* in the many-ScalarArrayOpExpr-scan case, and there it will be paid
4823+
* over the life of the scan node.
4824+
*/
4825+
*indexTotalCost+=num_sa_scans*100.0*cpu_operator_cost;
48054826

48064827
/*
48074828
* Generic assumption about index correlation: there isn't any.
@@ -4829,6 +4850,7 @@ btcostestimate(PG_FUNCTION_ARGS)
48294850
intindexcol;
48304851
booleqQualHere;
48314852
boolfound_saop;
4853+
doublenum_sa_scans;
48324854
ListCell*l;
48334855

48344856
/*
@@ -4852,6 +4874,7 @@ btcostestimate(PG_FUNCTION_ARGS)
48524874
indexcol=0;
48534875
eqQualHere= false;
48544876
found_saop= false;
4877+
num_sa_scans=1;
48554878
foreach(l,indexQuals)
48564879
{
48574880
RestrictInfo*rinfo= (RestrictInfo*)lfirst(l);
@@ -4928,6 +4951,15 @@ btcostestimate(PG_FUNCTION_ARGS)
49284951
Assert(op_strategy!=0);/* not a member of opclass?? */
49294952
if (op_strategy==BTEqualStrategyNumber)
49304953
eqQualHere= true;
4954+
/* count up number of SA scans induced by indexBoundQuals only */
4955+
if (IsA(clause,ScalarArrayOpExpr))
4956+
{
4957+
ScalarArrayOpExpr*saop= (ScalarArrayOpExpr*)clause;
4958+
intalength=estimate_array_length(lsecond(saop->args));
4959+
4960+
if (alength>1)
4961+
num_sa_scans *=alength;
4962+
}
49314963
indexBoundQuals=lappend(indexBoundQuals,rinfo);
49324964
}
49334965

@@ -4949,6 +4981,12 @@ btcostestimate(PG_FUNCTION_ARGS)
49494981
index->rel->relid,
49504982
JOIN_INNER);
49514983
numIndexTuples=btreeSelectivity*index->rel->tuples;
4984+
/*
4985+
* As in genericcostestimate(), we have to adjust for any
4986+
* ScalarArrayOpExpr quals included in indexBoundQuals, and then
4987+
* round to integer.
4988+
*/
4989+
numIndexTuples=rint(numIndexTuples /num_sa_scans);
49524990
}
49534991

49544992
genericcostestimate(root,index,indexQuals,outer_rel,numIndexTuples,

0 commit comments

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