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

Commit3f56ca1

Browse files
committed
Reduce the rescan cost estimate for Materialize nodes to cpu_operator_cost per
tuple, instead of the former cpu_tuple_cost. It is sane to charge less thancpu_tuple_cost because Materialize never does any qual-checking or projection,so it's got less overhead than most plan node types. In particular, we wantto have the same charge here as is charged for readout in cost_sort. Thatavoids the problem recently exhibited by Teodor wherein the planner prefersa useless sort over a materialize step in a context where a lot of rescanningwill happen. The rescan costs should be just about the same for both nodetypes, so make their estimates the same.Not back-patching because all of the current logic for rescan cost estimatesis new in 9.0. The old handling of rescans is sufficiently not-sane thatchanging this in that structure is a bit pointless, and might indeed causeregressions.
1 parent2f6cf91 commit3f56ca1

File tree

2 files changed

+49
-18
lines changed

2 files changed

+49
-18
lines changed

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

Lines changed: 45 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -59,7 +59,7 @@
5959
* Portions Copyright (c) 1994, Regents of the University of California
6060
*
6161
* IDENTIFICATION
62-
* $PostgreSQL: pgsql/src/backend/optimizer/path/costsize.c,v 1.214 2010/01/05 21:53:58 rhaas Exp $
62+
* $PostgreSQL: pgsql/src/backend/optimizer/path/costsize.c,v 1.215 2010/02/19 21:49:10 tgl Exp $
6363
*
6464
*-------------------------------------------------------------------------
6565
*/
@@ -1189,7 +1189,9 @@ cost_sort(Path *path, PlannerInfo *root,
11891189

11901190
/*
11911191
* Also charge a small amount (arbitrarily set equal to operator cost) per
1192-
* extracted tuple. Note it's correct to use tuples not output_tuples
1192+
* extracted tuple. We don't charge cpu_tuple_cost because a Sort node
1193+
* doesn't do qual-checking or projection, so it has less overhead than
1194+
* most plan nodes. Note it's correct to use tuples not output_tuples
11931195
* here --- the upper LIMIT will pro-rate the run cost so we'd be double
11941196
* counting the LIMIT otherwise.
11951197
*/
@@ -1222,14 +1224,18 @@ cost_material(Path *path,
12221224
longwork_mem_bytes=work_mem*1024L;
12231225

12241226
/*
1225-
* Whether spilling or not, charge 2x cpu_tuple_cost per tuple to reflect
1226-
* bookkeeping overhead. (This rate must be more than cpu_tuple_cost;
1227+
* Whether spilling or not, charge 2x cpu_operator_cost per tuple to
1228+
* reflect bookkeeping overhead. (This rate must be more than what
1229+
* cost_rescan charges for materialize, ie, cpu_operator_cost per tuple;
12271230
* if it is exactly the same then there will be a cost tie between
12281231
* nestloop with A outer, materialized B inner and nestloop with B outer,
12291232
* materialized A inner. The extra cost ensures we'll prefer
1230-
* materializing the smaller rel.)
1233+
* materializing the smaller rel.) Note that this is normally a good deal
1234+
* less than cpu_tuple_cost; which is OK because a Material plan node
1235+
* doesn't do qual-checking or projection, so it's got less overhead than
1236+
* most plan nodes.
12311237
*/
1232-
run_cost+=2*cpu_tuple_cost*tuples;
1238+
run_cost+=2*cpu_operator_cost*tuples;
12331239

12341240
/*
12351241
* If we will spill to disk, charge at the rate of seq_page_cost per page.
@@ -1830,19 +1836,19 @@ cost_mergejoin(MergePath *path, PlannerInfo *root, SpecialJoinInfo *sjinfo)
18301836
bare_inner_cost=inner_run_cost*rescanratio;
18311837
/*
18321838
* When we interpose a Material node the re-fetch cost is assumed to be
1833-
* justcpu_tuple_cost per tuple, independently of the underlying plan's
1834-
* cost;but wehave tocharge an extracpu_tuple_cost per original fetch
1835-
* as well. Note that we're assuming the materialize node will never
1836-
* spill to disk, since it only has to remember tuples back to the last
1837-
* mark. (If there are a huge number of duplicates, our other cost
1839+
* justcpu_operator_cost per tuple, independently of the underlying
1840+
*plan'scost;and we charge an extracpu_operator_cost per original
1841+
*fetchas well. Note that we're assuming the materialize node will
1842+
*neverspill to disk, since it only has to remember tuples back to the
1843+
*lastmark. (If there are a huge number of duplicates, our other cost
18381844
* factors will make the path so expensive that it probably won't get
18391845
* chosen anyway.) So we don't use cost_rescan here.
18401846
*
18411847
* Note: keep this estimate in sync with create_mergejoin_plan's labeling
18421848
* of the generated Material node.
18431849
*/
18441850
mat_inner_cost=inner_run_cost+
1845-
cpu_tuple_cost*inner_path_rows*rescanratio;
1851+
cpu_operator_cost*inner_path_rows*rescanratio;
18461852

18471853
/* Prefer materializing if it looks cheaper */
18481854
if (mat_inner_cost<bare_inner_cost)
@@ -2354,10 +2360,8 @@ cost_rescan(PlannerInfo *root, Path *path,
23542360
*rescan_startup_cost=0;
23552361
*rescan_total_cost=path->total_cost-path->startup_cost;
23562362
break;
2357-
caseT_Material:
23582363
caseT_CteScan:
23592364
caseT_WorkTableScan:
2360-
caseT_Sort:
23612365
{
23622366
/*
23632367
* These plan types materialize their final result in a
@@ -2381,6 +2385,33 @@ cost_rescan(PlannerInfo *root, Path *path,
23812385
*rescan_total_cost=run_cost;
23822386
}
23832387
break;
2388+
caseT_Material:
2389+
caseT_Sort:
2390+
{
2391+
/*
2392+
* These plan types not only materialize their results, but
2393+
* do not implement qual filtering or projection. So they
2394+
* are even cheaper to rescan than the ones above. We charge
2395+
* only cpu_operator_cost per tuple. (Note: keep that in
2396+
* sync with the run_cost charge in cost_sort, and also see
2397+
* comments in cost_material before you change it.)
2398+
*/
2399+
Costrun_cost=cpu_operator_cost*path->parent->rows;
2400+
doublenbytes=relation_byte_size(path->parent->rows,
2401+
path->parent->width);
2402+
longwork_mem_bytes=work_mem*1024L;
2403+
2404+
if (nbytes>work_mem_bytes)
2405+
{
2406+
/* It will spill, so account for re-read cost */
2407+
doublenpages=ceil(nbytes /BLCKSZ);
2408+
2409+
run_cost+=seq_page_cost*npages;
2410+
}
2411+
*rescan_startup_cost=0;
2412+
*rescan_total_cost=run_cost;
2413+
}
2414+
break;
23842415
default:
23852416
*rescan_startup_cost=path->startup_cost;
23862417
*rescan_total_cost=path->total_cost;

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

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -10,7 +10,7 @@
1010
*
1111
*
1212
* IDENTIFICATION
13-
* $PostgreSQL: pgsql/src/backend/optimizer/plan/createplan.c,v 1.271 2010/02/12 17:33:20 tgl Exp $
13+
* $PostgreSQL: pgsql/src/backend/optimizer/plan/createplan.c,v 1.272 2010/02/19 21:49:10 tgl Exp $
1414
*
1515
*-------------------------------------------------------------------------
1616
*/
@@ -1703,11 +1703,11 @@ create_mergejoin_plan(PlannerInfo *root,
17031703

17041704
/*
17051705
* We assume the materialize will not spill to disk, and therefore
1706-
* charge justcpu_tuple_cost per tuple. (Keep this estimate in sync
1707-
* with cost_mergejoin.)
1706+
* charge justcpu_operator_cost per tuple. (Keep this estimate in
1707+
*syncwith cost_mergejoin.)
17081708
*/
17091709
copy_plan_costsize(matplan,inner_plan);
1710-
matplan->total_cost+=cpu_tuple_cost*matplan->plan_rows;
1710+
matplan->total_cost+=cpu_operator_cost*matplan->plan_rows;
17111711

17121712
inner_plan=matplan;
17131713
}

0 commit comments

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