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

Commit0dd46a7

Browse files
committed
In planner, don't assume that empty parent tables aren't really empty.
There's a heuristic in estimate_rel_size() to clamp the minimum sizeestimate for a table to 10 pages, unless we can see that vacuum or analyzehas been run (and set relpages to something nonzero, so this will alwayshappen for a table that's actually empty). However, it would be betternot to do this for inheritance parent tables, which very commonly arereally empty and can be expected to stay that way. Per discussion of arecent pgsql-performance report from Anish Kejariwal. Also prevent itfrom happening for indexes (although this is more in the nature ofdocumentation, since CREATE INDEX normally initializes relpages tosomething nonzero anyway).Back-patch to 9.0, because the ability to collect statistics across awhole inheritance tree has improved the planner's estimates to the pointwhere this relatively small error makes a significant difference. In thereferenced report, merge or hash joins were incorrectly estimated ascheaper than a nestloop with inner indexscan on the inherited table.That was less likely before 9.0 because the lack of inherited stats wouldhave resulted in a default (and rather pessimistic) estimate of the costof a merge or hash join.
1 parent9a9e530 commit0dd46a7

File tree

1 file changed

+30
-14
lines changed

1 file changed

+30
-14
lines changed

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

Lines changed: 30 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -344,24 +344,38 @@ estimate_rel_size(Relation rel, int32 *attr_widths,
344344

345345
/*
346346
* HACK: if the relation has never yet been vacuumed, use a
347-
* minimum estimate of 10 pages. This emulates a desirable aspect
348-
* of pre-8.0 behavior, which is that we wouldn't assume a newly
349-
* created relation is really small, which saves us from making
350-
* really bad plans during initial data loading. (The plans are
351-
* not wrong when they are made, but if they are cached and used
352-
* again after the table has grown a lot, they are bad.) It would
353-
* be better to force replanning if the table size has changed a
354-
* lot since the plan was made ... but we don't currently have any
355-
* infrastructure for redoing cached plans at all, so we have to
356-
* kluge things here instead.
347+
* minimum size estimate of 10 pages. The idea here is to avoid
348+
* assuming a newly-created table is really small, even if it
349+
* currently is, because that may not be true once some data gets
350+
* loaded into it. Once a vacuum or analyze cycle has been done
351+
* on it, it's more reasonable to believe the size is somewhat
352+
* stable.
353+
*
354+
* (Note that this is only an issue if the plan gets cached and
355+
* used again after the table has been filled. What we're trying
356+
* to avoid is using a nestloop-type plan on a table that has
357+
* grown substantially since the plan was made. Normally,
358+
* autovacuum/autoanalyze will occur once enough inserts have
359+
* happened and cause cached-plan invalidation; but that doesn't
360+
* happen instantaneously, and it won't happen at all for cases
361+
* such as temporary tables.)
357362
*
358363
* We approximate "never vacuumed" by "has relpages = 0", which
359364
* means this will also fire on genuinely empty relations.Not
360365
* great, but fortunately that's a seldom-seen case in the real
361366
* world, and it shouldn't degrade the quality of the plan too
362367
* much anyway to err in this direction.
368+
*
369+
* There are two exceptions wherein we don't apply this heuristic.
370+
* One is if the table has inheritance children. Totally empty
371+
* parent tables are quite common, so we should be willing to
372+
* believe that they are empty. Also, we don't apply the 10-page
373+
* minimum to indexes.
363374
*/
364-
if (curpages<10&&rel->rd_rel->relpages==0)
375+
if (curpages<10&&
376+
rel->rd_rel->relpages==0&&
377+
!rel->rd_rel->relhassubclass&&
378+
rel->rd_rel->relkind!=RELKIND_INDEX)
365379
curpages=10;
366380

367381
/* report estimated # pages */
@@ -377,16 +391,18 @@ estimate_rel_size(Relation rel, int32 *attr_widths,
377391
reltuples= (double)rel->rd_rel->reltuples;
378392

379393
/*
380-
* If it's an index, discount the metapage. This is a kluge
381-
* because it assumes more than it ought to about index contents;
382-
* it's reasonably OK for btrees but a bit suspect otherwise.
394+
* If it's an index, discount the metapage while estimating the
395+
* number of tuples. This is a kluge because it assumes more than
396+
* it ought to about index structure. Currently it's OK for
397+
* btree, hash, and GIN indexes but suspect for GiST indexes.
383398
*/
384399
if (rel->rd_rel->relkind==RELKIND_INDEX&&
385400
relpages>0)
386401
{
387402
curpages--;
388403
relpages--;
389404
}
405+
390406
/* estimate number of tuples from previous tuple density */
391407
if (relpages>0)
392408
density=reltuples / (double)relpages;

0 commit comments

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