8000 Clean up some bogosities in path cost estimation, like · postgrespro/postgres_cluster@7a7ba33 · GitHub
[go: up one dir, main page]
Skip to content

Commit 7a7ba33

Browse files
tglsfdctglsfdc
committed
Clean up some bogosities in path cost estimation, like
sometimes estimating an index scan of a table to be cheaper than a sequential scan of the same tuples...
1 parent 11a0027 commit 7a7ba33

File tree

1 file changed

+49
-28
lines changed

1 file changed

+49
-28
lines changed

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

Lines changed: 49 additions & 28 deletions
< 8000 td data-grid-cell-id="diff-a9c2b2d65d0e5ce5cd03ed1e0008bd00d4954c8e8214bc9090fb8d342ea98c29-192-202-2" data-line-anchor="diff-a9c2b2d65d0e5ce5cd03ed1e0008bd00d4954c8e8214bc9090fb8d342ea98c29R202" data-selected="false" role="gridcell" style="background-color:var(--diffBlob-additionLine-bgColor, var(--diffBlob-addition-bgColor-line));padding-right:24px" tabindex="-1" valign="top" class="focusable-grid-cell diff-text-cell right-side-diff-cell left-side">+
*/
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
*
88
*
99
* IDENTIFICATION
10-
* $Header: /cvsroot/pgsql/src/backend/optimizer/path/costsize.c,v 1.34 1999/04/05 02:07:07 tgl Exp $
10+
* $Header: /cvsroot/pgsql/src/backend/optimizer/path/costsize.c,v 1.35 1999/04/30 04:01:44 tgl Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -129,28 +129,36 @@ cost_index(Oid indexid,
129129
int indextuples,
130130
bool is_injoin)
131131
{
132-
Cost temp;
133-
double temp2;
134-
135-
temp = (Cost) 0;
132+
Cost temp = 0;
136133

137134
if (!_enable_indexscan_ && !is_injoin)
138135
temp += _disable_cost_;
139136

137+
/* We want to be sure we estimate the cost of an index scan
138+
* as more than the cost of a sequential scan (when selec == 1.0),
139+
* even if we don't have good stats. So, disbelieve zero index size.
140+
*/
141+
if (expected_indexpages <= 0)
142+
expected_indexpages = 1;
143+
if (indextuples <= 0)
144+
indextuples = 1;
145+
140146
/* expected index relation pages */
141147
temp += expected_indexpages;
142148

143-
/* expected base relation pages */
144-
temp2 = (reltuples == 0) ? (double) 0 : (double) relpages / reltuples;
145-
temp2 = temp2 * (double) selec *indextuples;
146-
147-
temp += Min(relpages, (int) ceil(temp2));
149+
/* expected base relation pages
150+
* XXX this isn't really right, since we will access the table
151+
* nonsequentially and might have to fetch the same page
152+
* more than once. This calculation assumes the buffer cache
153+
* will prevent that from happening...
154+
*/
155+
temp += ceil(((double) selec) * ((double) relpages));
148156

149157
/* per index tuples */
150-
temp = temp + (_cpu_index_page_wight_ * selec * indextuples);
158+
temp += _cpu_index_page_wight_ * selec * indextuples;
151159

152160
/* per heap tuples */
153-
temp = temp + (_cpu_page_wight_ * selec * reltuples);
161+
temp += _cpu_page_wight_ * selec * reltuples;
154162

155163
Assert(temp >= 0);
156164
return temp;
@@ -168,8 +176,13 @@ cost_index(Oid indexid,
168176
* 'pathkeys' is a list of sort keys
169177
* 'tuples' is the number of tuples in the relation
170178
* 'width' is the average tuple width in bytes
171-
* 'noread' is a flag indicating that the sort result can remain on disk
172-
* (i.e., the sort result is the result relation)
179+
* 'noread' is a flag indicating that the cost of reading the sort
180+
* source data should not be included (i.e., the caller
181+
* will account for it separately).
182+
*
183+
* NOTE: some callers currently pass NULL for pathkeys because they
184+
* can't conveniently supply sort keys. Since this routine doesn't
185+
* currently do anything with pathkeys anyway, that doesn't matter...
173186
*
174187
* Returns a flonum.
175188
*
@@ -179,28 +192,33 @@ cost_sort(List *pathkeys, int tuples, int width, bool noread)
179192
{
180193
Cost temp = 0;
181194
int npages = page_size(tuples, width);
182-
Cost pages = (Cost) npages;
183-
Cost numTuples = tuples;
195+
double log_npages;
184196

185197
if (!_enable_sort_)
186198
temp += _disable_cost_;
187-
if (tuples == 0 || pathkeys == NULL)
188-
{
189-
Assert(temp >= 0);
190-
return temp;
191-
}
192-
temp += pages * base_log((double) pages, (double) 2.0);
199+
200+
/* We want to be sure the cost of a sort is never estimated as zero,
201+
* even if passed-in tuple count is zero. Besides, mustn't do log(0)...
202
203+
if (npages <= 0)
204+
npages = 1;
205+
206+
log_npages = ceil(base_log((double) npages, 2.0));
207+
if (log_npages <= 0.0)
208+
log_npages = 1.0;
209+
210+
temp += npages * log_npages;
193211

194212
/*
195213
* could be base_log(pages, NBuffers), but we are only doing 2-way
196214
* merges
197215
*/
198-
temp += _cpu_page_wight_ * numTuples *
199-
base_log((double) pages, (double) 2.0);
216+
temp += _cpu_page_wight_ * tuples * log_npages;
200217

201218
if (!noread)
202-
temp = temp + cost_seqscan(_NONAME_RELATION_ID_, npages, tuples);
203-
Assert(temp >= 0);
219+
temp += cost_seqscan(_NONAME_RELATION_ID_, npages, tuples);
220+
221+
Assert(temp > 0);
204222

205223
return temp;
206224
}
@@ -290,9 +308,12 @@ cost_mergejoin(Cost outercost,
290308

291309
temp += outercost;
292310
temp += innercost;
293-
temp += cost_sort(outersortkeys, outersize, outerwidth, false);
294-
temp += cost_sort(innersortkeys, innersize, innerwidth, false);
311+
if (outersortkeys) /* do we need to sort? */
312+
temp += cost_sort(outersortkeys, outersize, outerwidth, true);
313+
if (innersortkeys) /* do we need to sort? */
314+
temp += cost_sort(innersortkeys, innersize, innerwidth, true);
295315
temp += _cpu_page_wight_ * (outersize + innersize);
316+
296317
Assert(temp >= 0);
297318

298319
return temp;

0 commit comments

Comments
 (0)
0