Skip to content

Commit b74c543

Browse files
tglsfdctglsfdc
committed
Improve usage of effective_cache_size parameter by assuming that all the
tables in the query compete for cache space, not just the one we are currently costing an indexscan for. This seems more realistic, and it definitely will help in examples recently exhibited by Stefan Kaltenbrunner. To get the total size of all the tables involved, we must tweak the handling of 'append relations' a bit --- formerly we looked up information about the child tables on-the-fly during set_append_rel_pathlist, but it needs to be done before we start doing any cost estimation, so push it into the add_base_rels_to_query scan.
1 parent 45e11d0 commit b74c543

File tree

11 files changed

+171
-44
lines changed

11 files changed

+171
-44
lines changed

‎src/backend/nodes/outfuncs.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/nodes/outfuncs.c,v 1.284 2006/08/25 04:06:50 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/nodes/outfuncs.c,v 1.285 2006/09/19 22:49:52 tgl Exp $
1212
*
1313
* NOTES
1414
* Every node type that can appear in stored rules' parsetrees *must*
@@ -1187,6 +1187,7 @@ _outPlannerInfo(StringInfo str, PlannerInfo *node)
11871187
WRITE_NODE_FIELD(query_pathkeys);
11881188
WRITE_NODE_FIELD(group_pathkeys);
11891189
WRITE_NODE_FIELD(sort_pathkeys);
1190+
WRITE_FLOAT_FIELD(total_table_pages, "%.0f");
11901191
WRITE_FLOAT_FIELD(tuple_fraction, "%.4f");
11911192
WRITE_BOOL_FIELD(hasJoinRTEs);
11921193
WRITE_BOOL_FIELD(hasOuterJoins);

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

Lines changed: 5 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/optimizer/path/allpaths.c,v 1.152 2006/08/19 02:48:53 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/optimizer/path/allpaths.c,v 1.153 2006/09/19 22:49:52 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -279,13 +279,6 @@ set_append_rel_pathlist(PlannerInfo *root, RelOptInfo *rel,
279279
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
280280
errmsg("SELECT FOR UPDATE/SHARE is not supported for inheritance queries")));
281281

282-
/*
283-
* We might have looked up indexes for the parent rel, but they're
284-
* really not relevant to the appendrel. Reset the pointer to avoid
285-
* any confusion.
286-
*/
287-
rel->indexlist = NIL;
288-
289282
/*
290283
* Initialize to compute size estimates for whole append relation
291284
*/
@@ -312,11 +305,11 @@ set_append_rel_pathlist(PlannerInfo *root, RelOptInfo *rel,
312305
childRTindex = appinfo->child_relid;
313306

314307
/*
315-
* Make a RelOptInfo for the child so we can do planning. Mark it as
316-
* an "other rel" since it will not be part of the main join tree.
308+
* The child rel's RelOptInfo was already created during
309+
* add_base_rels_to_query.
317310
*/
318-
childrel = build_simple_rel(root, childRTindex,
319-
RELOPT_OTHER_MEMBER_REL);
311+
childrel = find_base_rel(root, childRTindex);
312+
Assert(childrel->reloptkind == RELOPT_OTHER_MEMBER_REL);
320313

321314
/*
322315
* Copy the parent's targetlist and quals to the child, with

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

Lines changed: 70 additions & 11 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.165 2006/08/02 01:59:45 joe Exp $
57+
* $PostgreSQL: pgsql/src/backend/optimizer/path/costsize.c,v 1.166 2006/09/19 22:49:52 tgl Exp $
5858
*
5959
*-------------------------------------------------------------------------
6060
*/
@@ -288,7 +288,8 @@ cost_index(IndexPath *path, PlannerInfo *root,
288288

289289
pages_fetched = index_pages_fetched(tuples_fetched * num_scans,
290290
baserel->pages,
291-
index->pages);
291+
(double) index->pages,
292+
root);
292293

293294
run_cost += (pages_fetched * random_page_cost) / num_scans;
294295
}
@@ -300,7 +301,8 @@ cost_index(IndexPath *path, PlannerInfo *root,
300301
*/
301302
pages_fetched = index_pages_fetched(tuples_fetched,
302303
baserel->pages,
303-
index->pages);
304+
(double) index->pages,
305+
root);
304306

305307
/* max_IO_cost is for the perfectly uncorrelated case (csquared=0) */
306308
max_IO_cost = pages_fetched * random_page_cost;
@@ -369,31 +371,42 @@ cost_index(IndexPath *path, PlannerInfo *root,
369371
* b = # buffer pages available (we include kernel space here)
370372
*
371373
* We assume that effective_cache_size is the total number of buffer pages
372-
* available for both table and index, and pro-rate that space between the
373-
* table and index. (Ideally other_pages should include all the other
374-
* tables and indexes used by the query too; but we don't have a good way
375-
* to get that number here.)
374+
* available for the whole query, and pro-rate that space across all the
375+
* tables in the query and the index currently under consideration. (This
376+
* ignores space needed for other indexes used by the query, but since we
377+
* don't know which indexes will get used, we can't estimate that very well;
378+
* and in any case counting all the tables may well be an overestimate, since
379+
* depending on the join plan not all the tables may be scanned concurrently.)
376380
*
377381
* The product Ns is the number of tuples fetched; we pass in that
378-
* product rather than calculating it here.
382+
* product rather than calculating it here. "pages" is the number of pages
383+
* in the object under consideration (either an index or a table).
384+
* "index_pages" is the amount to add to the total table space, which was
385+
* computed for us by query_planner.
379386
*
380387
* Caller is expected to have ensured that tuples_fetched is greater than zero
381388
* and rounded to integer (see clamp_row_est). The result will likewise be
382389
* greater than zero and integral.
383390
*/
384391
double
385392
index_pages_fetched(double tuples_fetched, BlockNumber pages,
386-
BlockNumber other_pages)
393+
double index_pages, PlannerInfo *root)
387394
{
388395
double pages_fetched;
396+
double total_pages;
389397
double T,
390398
b;
391399

392400
/* T is # pages in table, but don't allow it to be zero */
393401
T = (pages > 1) ? (double) pages : 1.0;
394402

403+
/* Compute number of pages assumed to be competing for cache space */
404+
total_pages = root->total_table_pages + index_pages;
405+
total_pages = Max(total_pages, 1.0);
406+
Assert(T <= total_pages);
407+
395408
/* b is pro-rated share of effective_cache_size */
396-
b = (double) effective_cache_size * T / (T + (double) other_pages);
409+
b = (double) effective_cache_size * T / total_pages;
397410
/* force it positive and integral */
398411
if (b <= 1.0)
399412
b = 1.0;
@@ -430,6 +443,51 @@ index_pages_fetched(double tuples_fetched, BlockNumber pages,
430443
return pages_fetched;
431444
}
432445

446+
/*
447+
* get_indexpath_pages
448+
* Determine the total size of the indexes used in a bitmap index path.
449+
*
450+
* Note: if the same index is used more than once in a bitmap tree, we will
451+
* count it multiple times, which perhaps is the wrong thing ... but it's
452+
* not completely clear, and detecting duplicates is difficult, so ignore it
453+
* for now.
454+
*/
455+
static double
456+
get_indexpath_pages(Path *bitmapqual)
457+
{
458+
double result = 0;
459+
ListCell *l;
460+
461+
if (IsA(bitmapqual, BitmapAndPath))
462+
{
463+
BitmapAndPath *apath = (BitmapAndPath *) bitmapqual;
464+
465+
foreach(l, apath->bitmapquals)
466+
{
467+
result += get_indexpath_pages((Path *) lfirst(l));
468+
}
469+
}
470+
else if (IsA(bitmapqual, BitmapOrPath))
471+
{
472+
BitmapOrPath *opath = (BitmapOrPath *) bitmapqual;
473+
474+
foreach(l, opath->bitmapquals)
475+
{
476+
result += get_indexpath_pages((Path *) lfirst(l));
477+
}
478+
}
479+
else if (IsA(bitmapqual, IndexPath))
480+
{
481+
IndexPath *ipath = (IndexPath *) bitmapqual;
482+
483+
result = (double) ipath->indexinfo->pages;
484+
}
485+
else
486+
elog(ERROR, "unrecognized node type: %d", nodeTag(bitmapqual));
487+
488+
return result;
489+
}
490+
433491
/*
434492
* cost_bitmap_heap_scan
435493
* Determines and returns the cost of scanning a relation using a bitmap
@@ -494,7 +552,8 @@ cost_bitmap_heap_scan(Path *path, PlannerInfo *root, RelOptInfo *baserel,
494552

495553
pages_fetched = index_pages_fetched(tuples_fetched * num_scans,
496554
baserel->pages,
497-
0 /* XXX total index size? */);
555+
get_indexpath_pages(bitmapqual),
556+
root);
498557
pages_fetched /= num_scans;
499558
}
500559
else

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

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/optimizer/plan/initsplan.c,v 1.121 2006/09/08 17:49:13 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/optimizer/plan/initsplan.c,v 1.122 2006/09/19 22:49:52 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -76,8 +76,8 @@ static void check_hashjoinable(RestrictInfo *restrictinfo);
7676
* At the end of this process, there should be one baserel RelOptInfo for
7777
* every non-join RTE that is used in the query. Therefore, this routine
7878
* is the only place that should call build_simple_rel with reloptkind
79-
* RELOPT_BASEREL. However, otherrels will be built later for append relation
80-
* members.
79+
* RELOPT_BASEREL. (Note: build_simple_rel recurses internally to build
80+
* "other rel" RelOptInfos for the members of any appendrels we find here.)
8181
*/
8282
void
8383
add_base_rels_to_query(PlannerInfo *root, Node *jtnode)

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

Lines changed: 37 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -14,7 +14,7 @@
1414
*
1515
*
1616
* IDENTIFICATION
17-
* $PostgreSQL: pgsql/src/backend/optimizer/plan/planmain.c,v 1.95 2006/07/14 14:52:20 momjian Exp $
17+
* $PostgreSQL: pgsql/src/backend/optimizer/plan/planmain.c,v 1.96 2006/09/19 22:49:52 tgl Exp $
1818
*
1919
*-------------------------------------------------------------------------
2020
*/
@@ -85,6 +85,8 @@ query_planner(PlannerInfo *root, List *tlist, double tuple_fraction,
8585
RelOptInfo *final_rel;
8686
Path *cheapestpath;
8787
Path *sortedpath;
88+
Index rti;
89+
double total_pages;
8890

8991
/* Make tuple_fraction accessible to lower-level routines */
9092
root->tuple_fraction = tuple_fraction;
@@ -122,10 +124,43 @@ query_planner(PlannerInfo *root, List *tlist, double tuple_fraction,
122124
root->oj_info_list = NIL;
123125

124126
/*
125-
* Construct RelOptInfo nodes for all base relations in query.
127+
* Construct RelOptInfo nodes for all base relations in query, and
128+
* indirectly for all appendrel member relations ("other rels"). This
129+
* will give us a RelOptInfo for every "simple" (non-join) rel involved
130+
* in the query.
131+
*
132+
* Note: the reason we find the rels by searching the jointree and
133+
* appendrel list, rather than just scanning the rangetable, is that the
134+
* rangetable may contain RTEs for rels not actively part of the query,
135+
* for example views. We don't want to make RelOptInfos for them.
126136
*/
127137
add_base_rels_to_query(root, (Node *) parse->jointree);
128138

139+
/*
140+
* We should now have size estimates for every actual table involved
141+
* in the query, so we can compute total_table_pages. Note that
142+
* appendrels are not double-counted here, even though we don't bother
143+
* to distinguish RelOptInfos for appendrel parents, because the parents
144+
* will still have size zero.
145+
*
146+
* XXX if a table is self-joined, we will count it once per appearance,
147+
* which perhaps is the wrong thing ... but that's not completely clear,
148+
* and detecting self-joins here is difficult, so ignore it for now.
149+
*/
150+
total_pages = 0;
151+
for (rti = 1; rti < root->simple_rel_array_size; rti++)
152+
{
153+
RelOptInfo *brel = root->simple_rel_array[rti];
154+
155+
if (brel == NULL)
156+
continue;
157+
158+
Assert(brel->relid == rti); /* sanity check on array */
159+
160+
total_pages += (double) brel->pages;
161+
}
162+
root->total_table_pages = total_pages;
163+
129164
/*
130165
* Examine the targetlist and qualifications, adding entries to baserel
131166
* targetlists for all referenced Vars. Restrict and join clauses are

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

Lines changed: 18 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -9,7 +9,7 @@
99
*
1010
*
1111
* IDENTIFICATION
12-
* $PostgreSQL: pgsql/src/backend/optimizer/util/plancat.c,v 1.125 2006/08/25 04:06:50 tgl Exp $
12+
* $PostgreSQL: pgsql/src/backend/optimizer/util/plancat.c,v 1.126 2006/09/19 22:49:53 tgl Exp $
1313
*
1414
*-------------------------------------------------------------------------
1515
*/
@@ -62,9 +62,15 @@ static List *get_relation_constraints(Oid relationObjectId, RelOptInfo *rel);
6262
* Also, initialize the attr_needed[] and attr_widths[] arrays. In most
6363
* cases these are left as zeroes, but sometimes we need to compute attr
6464
* widths here, and we may as well cache the results for costsize.c.
65+
*
66+
* If inhparent is true, all we need to do is set up the attr arrays:
67+
* the RelOptInfo actually represents the appendrel formed by an inheritance
68+
* tree, and so the parent rel's physical size and index information isn't
69+
* important for it.
6570
*/
6671
void
67-
get_relation_info(PlannerInfo *root, Oid relationObjectId, RelOptInfo *rel)
72+
get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent,
73+
RelOptInfo *rel)
6874
{
6975
Index varno = rel->relid;
7076
Relation relation;
@@ -88,15 +94,21 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, RelOptInfo *rel)
8894
palloc0((rel->max_attr - rel->min_attr + 1) * sizeof(int32));
8995

9096
/*
91-
* Estimate relation size.
97+
* Estimate relation size --- unless it's an inheritance parent, in which
98+
* case the size will be computed later in set_append_rel_pathlist, and
99+
* we must leave it zero for now to avoid bollixing the total_table_pages
100+
* calculation.
92101
*/
93-
estimate_rel_size(relation, rel->attr_widths - rel->min_attr,
94-
&rel->pages, &rel->tuples);
102+
if (!inhparent)
103+
estimate_rel_size(relation, rel->attr_widths - rel->min_attr,
104+
&rel->pages, &rel->tuples);
95105

96106
/*
97107
* Make list of indexes. Ignore indexes on system catalogs if told to.
108+
* Don't bother with indexes for an inheritance parent, either.
98109
*/
99-
if (IgnoreSystemIndexes && IsSystemClass(relation->rd_rel))
110+
if (inhparent ||
111+
(IgnoreSystemIndexes && IsSystemClass(relation->rd_rel)))
100112
hasindex = false;
101113
else
102114
hasindex = relation->rd_rel->relhasindex;

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

Lines changed: 25 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/optimizer/util/relnode.c,v 1.81 2006/08/02 01:59:46 joe Exp $
11+
* $PostgreSQL: pgsql/src/backend/optimizer/util/relnode.c,v 1.82 2006/09/19 22:49:53 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -92,7 +92,7 @@ build_simple_rel(PlannerInfo *root, int relid, RelOptKind reloptkind)
9292
{
9393
case RTE_RELATION:
9494
/* Table --- retrieve statistics from the system catalogs */
95-
get_relation_info(root, rte->relid, rel);
95+
get_relation_info(root, rte->relid, rte->inh, rel);
9696
break;
9797
case RTE_SUBQUERY:
9898
case RTE_FUNCTION:
@@ -119,6 +119,29 @@ build_simple_rel(PlannerInfo *root, int relid, RelOptKind reloptkind)
119119
/* Save the finished struct in the query's simple_rel_array */
120120
root->simple_rel_array[relid] = rel;
121121

122+
/*
123+
* If this rel is an appendrel parent, recurse to build "other rel"
124+
* RelOptInfos for its children. They are "other rels" because they are
125+
* not in the main join tree, but we will need RelOptInfos to plan access
126+
* to them.
127+
*/
128+
if (rte->inh)
129+
{
130+
ListCell *l;
131+
132+
foreach(l, root->append_rel_list)
133+
{
134+
AppendRelInfo *appinfo = (AppendRelInfo *) lfirst(l);
135+
136+
/* append_rel_list contains all append rels; ignore others */
137+
if (appinfo->parent_relid != relid)
138+
continue;
139+
140+
(void) build_simple_rel(root, appinfo->child_relid,
141+
RELOPT_OTHER_MEMBER_REL);
142+
}
143+
}
144+
122145
return rel;
123146
}
124147

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

Lines changed: 3 additions & 2 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.211 2006/07/26 17:17:28 momjian Exp $
18+
* $PostgreSQL: pgsql/src/backend/utils/adt/selfuncs.c,v 1.212 2006/09/19 22:49:53 tgl Exp $
1919
*
2020
*-------------------------------------------------------------------------
2121
*/
@@ -4647,7 +4647,8 @@ genericcostestimate(PlannerInfo *root,
46474647
/* use Mackert and Lohman formula to adjust for cache effects */
46484648
pages_fetched = index_pages_fetched(pages_fetched,
46494649
index->pages,
4650-
index->rel->pages);
4650+
(double) index->pages,
4651+
root);
46514652

46524653
/*
46534654
* Now compute the total disk access cost, and then report a

0 commit comments

Comments
 (0)