1 /*-------------------------------------------------------------------------
4 * Routines to compute (and set) relation sizes and path costs
6 * Copyright (c) 1994, Regents of the University of California
10 * $Header: /cvsroot/pgsql/src/backend/optimizer/path/costsize.c,v 1.43 1999/07/16 04:59:14 momjian Exp $
12 *-------------------------------------------------------------------------
21 #define MAXINT INT_MAX
30 #include "optimizer/cost.h"
31 #include "optimizer/internal.h"
32 #include "optimizer/tlist.h"
33 #include "utils/lsyscache.h"
37 static int compute_attribute_width(TargetEntry *tlistentry);
38 static double relation_byte_size(int tuples, int width);
39 static double base_log(double x, double b);
40 static int compute_targetlist_width(List *targetlist);
42 int _disable_cost_ = 30000000;
44 bool _enable_seqscan_ = true;
45 bool _enable_indexscan_ = true;
46 bool _enable_sort_ = true;
47 bool _enable_hash_ = true;
48 bool _enable_nestloop_ = true;
49 bool _enable_mergejoin_ = true;
50 bool _enable_hashjoin_ = true;
52 Cost _cpu_page_weight_ = _CPU_PAGE_WEIGHT_;
53 Cost _cpu_index_page_weight_ = _CPU_INDEX_PAGE_WEIGHT_;
57 * Determines and returns the cost of scanning a relation sequentially.
58 * If the relation is a temporary to be materialized from a query
59 * embedded within a data field (determined by 'relid' containing an
60 * attribute reference), then a predetermined constant is returned (we
61 * have NO IDEA how big the result of a POSTQUEL procedure is going to
65 * cpu = *CPU-PAGE-WEIGHT* * t
67 * 'relid' is the relid of the relation to be scanned
68 * 'relpages' is the number of pages in the relation to be scanned
69 * (as determined from the system catalogs)
70 * 'reltuples' is the number of tuples in the relation to be scanned
76 cost_seqscan(int relid, int relpages, int reltuples)
80 if (!_enable_seqscan_)
81 temp += _disable_cost_;
87 * cost of sequentially scanning a materialized temporary relation
89 temp += _NONAME_SCAN_COST_;
94 temp += _cpu_page_weight_ * reltuples;
103 * Determines and returns the cost of scanning a relation using an index.
105 * disk = expected-index-pages + expected-data-pages
106 * cpu = *CPU-PAGE-WEIGHT* *
107 * (expected-index-tuples + expected-data-tuples)
109 * 'indexid' is the index OID
110 * 'expected-indexpages' is the number of index pages examined in the scan
111 * 'selec' is the selectivity of the index
112 * 'relpages' is the number of pages in the main relation
113 * 'reltuples' is the number of tuples in the main relation
114 * 'indexpages' is the number of pages in the index relation
115 * 'indextuples' is the number of tuples in the index relation
121 cost_index(Oid indexid,
122 int expected_indexpages,
132 if (!_enable_indexscan_ && !is_injoin)
133 temp += _disable_cost_;
136 * We want to be sure we estimate the cost of an index scan as more
137 * than the cost of a sequential scan (when selec == 1.0), even if we
138 * don't have good stats. So, disbelieve zero index size.
140 if (expected_indexpages <= 0)
141 expected_indexpages = 1;
142 if (indextuples <= 0)
145 /* expected index relation pages */
146 temp += expected_indexpages;
149 * expected base relation pages XXX this isn't really right, since we
150 * will access the table nonsequentially and might have to fetch the
151 * same page more than once. This calculation assumes the buffer
152 * cache will prevent that from happening...
154 temp += ceil(((double) selec) * ((double) relpages));
156 /* per index tuples */
157 temp += _cpu_index_page_weight_ * selec * indextuples;
159 /* per heap tuples */
160 temp += _cpu_page_weight_ * selec * reltuples;
168 * Determines and returns the cost of sorting a relation by considering
169 * the cost of doing an external sort: XXX this is probably too low
171 * cpu = *CPU-PAGE-WEIGHT* * (t lg t)
173 * 'pathkeys' is a list of sort keys
174 * 'tuples' is the number of tuples in the relation
175 * 'width' is the average tuple width in bytes
177 * NOTE: some callers currently pass NULL for pathkeys because they
178 * can't conveniently supply the sort keys. Since this routine doesn't
179 * currently do anything with pathkeys anyway, that doesn't matter...
180 * but if it ever does, it should react gracefully to lack of key data.
185 cost_sort(List *pathkeys, int tuples, int width)
188 int npages = page_size(tuples, width);
192 temp += _disable_cost_;
195 * We want to be sure the cost of a sort is never estimated as zero,
196 * even if passed-in tuple count is zero. Besides, mustn't do
204 log_npages = ceil(base_log((double) npages, 2.0));
205 if (log_npages <= 0.0)
208 temp += npages * log_npages;
211 * could be base_log(tuples, NBuffers), but we are only doing 2-way
214 temp += _cpu_page_weight_ * tuples * base_log((double) tuples, 2.0);
224 * Determines and returns the cost of writing a relation of 'tuples'
225 * tuples of 'width' bytes out to a result relation.
232 cost_result(int tuples, int width)
236 temp = temp + page_size(tuples, width);
237 temp = temp + _cpu_page_weight_ * tuples;
246 * Determines and returns the cost of joining two relations using the
247 * nested loop algorithm.
249 * 'outercost' is the (disk+cpu) cost of scanning the outer relation
250 * 'innercost' is the (disk+cpu) cost of scanning the inner relation
251 * 'outertuples' is the number of tuples in the outer relation
257 cost_nestloop(Cost outercost,
266 if (!_enable_nestloop_)
267 temp += _disable_cost_;
269 temp += outertuples * innercost;
277 * 'outercost' and 'innercost' are the (disk+cpu) costs of scanning the
278 * outer and inner relations
279 * 'outersortkeys' and 'innersortkeys' are lists of the keys to be used
280 * to sort the outer and inner relations (or NIL if no explicit
281 * sort is needed because the source path is already ordered)
282 * 'outertuples' and 'innertuples' are the number of tuples in the outer
283 * and inner relations
284 * 'outerwidth' and 'innerwidth' are the (typical) widths (in bytes)
285 * of the tuples of the outer and inner relations
291 cost_mergejoin(Cost outercost,
302 if (!_enable_mergejoin_)
303 temp += _disable_cost_;
307 if (outersortkeys) /* do we need to sort? */
308 temp += cost_sort(outersortkeys, outersize, outerwidth);
309 if (innersortkeys) /* do we need to sort? */
310 temp += cost_sort(innersortkeys, innersize, innerwidth);
311 temp += _cpu_page_weight_ * (outersize + innersize);
319 * cost_hashjoin-- XXX HASH
320 * 'outercost' and 'innercost' are the (disk+cpu) costs of scanning the
321 * outer and inner relations
322 * 'outerkeys' and 'innerkeys' are lists of the keys to be used
323 * to hash the outer and inner relations
324 * 'outersize' and 'innersize' are the number of tuples in the outer
325 * and inner relations
326 * 'outerwidth' and 'innerwidth' are the (typical) widths (in bytes)
327 * of the tuples of the outer and inner relations
332 cost_hashjoin(Cost outercost,
342 int outerpages = page_size(outersize, outerwidth);
343 int innerpages = page_size(innersize, innerwidth);
345 if (!_enable_hashjoin_)
346 temp += _disable_cost_;
349 * Bias against putting larger relation on inside.
351 * Code used to use "outerpages < innerpages" but that has poor
352 * resolution when both relations are small.
354 if (relation_byte_size(outersize, outerwidth) <
355 relation_byte_size(innersize, innerwidth))
356 temp += _disable_cost_;
358 /* cost of source data */
359 temp += outercost + innercost;
361 /* cost of computing hash function: must do it once per tuple */
362 temp += _cpu_page_weight_ * (outersize + innersize);
364 /* cost of main-memory hashtable */
365 temp += (innerpages < NBuffers) ? innerpages : NBuffers;
368 * if inner relation is too big then we will need to "batch" the join,
369 * which implies writing and reading most of the tuples to disk an
372 if (innerpages > NBuffers)
373 temp += 2 * (outerpages + innerpages);
382 * Computes the size of each relation in 'rel_list' (after applying
383 * restrictions), by multiplying the selectivity of each restriction
384 * by the original size of the relation.
386 * Sets the 'size' field for each relation entry with this computed size.
391 compute_rel_size(RelOptInfo *rel)
396 temp = rel->tuples * product_selec(rel->restrictinfo);
398 if (temp >= (MAXINT - 1))
401 temp1 = ceil((double) temp);
403 Assert(temp1 <= MAXINT);
409 * Computes the width in bytes of a tuple from 'rel'.
411 * Returns the width of the tuple as a fixnum.
414 compute_rel_width(RelOptInfo *rel)
416 return compute_targetlist_width(get_actual_tlist(rel->targetlist));
420 * compute_targetlist_width
421 * Computes the width in bytes of a tuple made from 'targetlist'.
423 * Returns the width of the tuple as a fixnum.
426 compute_targetlist_width(List *targetlist)
431 foreach(temp_tl, targetlist)
433 tuple_width = tuple_width +
434 compute_attribute_width(lfirst(temp_tl));
440 * compute_attribute_width
441 * Given a target list entry, find the size in bytes of the attribute.
443 * If a field is variable-length, it is assumed to be at least the size
446 * Returns the width of the attribute as a fixnum.
449 compute_attribute_width(TargetEntry *tlistentry)
451 int width = get_typlen(tlistentry->resdom->restype);
454 return _DEFAULT_ATTRIBUTE_WIDTH_;
460 * compute_joinrel_size
461 * Computes the size of the join relation 'joinrel'.
466 compute_joinrel_size(JoinPath *joinpath)
471 /* cartesian product */
472 temp *= ((Path *) joinpath->outerjoinpath)->parent->size;
473 temp *= ((Path *) joinpath->innerjoinpath)->parent->size;
475 temp = temp * product_selec(joinpath->pathinfo);
476 if (temp >= (MAXINT - 1) / 2)
478 /* if we exceed (MAXINT-1)/2, we switch to log scale */
479 /* +1 prevents log(0) */
480 temp1 = ceil(log(temp + 1 - (MAXINT - 1) / 2) + (MAXINT - 1) / 2);
483 temp1 = ceil((double) temp);
491 * Estimate the storage space in bytes for a given number of tuples
492 * of a given width (size in bytes).
493 * To avoid overflow with big relations, result is a double.
497 relation_byte_size(int tuples, int width)
499 return ((double) tuples) * ((double) (width + sizeof(HeapTupleData)));
504 * Returns an estimate of the number of pages covered by a given
505 * number of tuples of a given width (size in bytes).
508 page_size(int tuples, int width)
512 temp = (int) ceil(relation_byte_size(tuples, width) / BLCKSZ);
518 base_log(double x, double b)
520 return log(x) / log(b);