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

Commitf3ff043

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 parentc529f88 commitf3ff043

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
@@ -385,24 +385,38 @@ estimate_rel_size(Relation rel, int32 *attr_widths,
385385

386386
/*
387387
* HACK: if the relation has never yet been vacuumed, use a
388-
* minimum estimate of 10 pages. This emulates a desirable aspect
389-
* of pre-8.0 behavior, which is that we wouldn't assume a newly
390-
* created relation is really small, which saves us from making
391-
* really bad plans during initial data loading. (The plans are
392-
* not wrong when they are made, but if they are cached and used
393-
* again after the table has grown a lot, they are bad.) It would
394-
* be better to force replanning if the table size has changed a
395-
* lot since the plan was made ... but we don't currently have any
396-
* infrastructure for redoing cached plans at all, so we have to
397-
* kluge things here instead.
388+
* minimum size estimate of 10 pages. The idea here is to avoid
389+
* assuming a newly-created table is really small, even if it
390+
* currently is, because that may not be true once some data gets
391+
* loaded into it. Once a vacuum or analyze cycle has been done
392+
* on it, it's more reasonable to believe the size is somewhat
393+
* stable.
394+
*
395+
* (Note that this is only an issue if the plan gets cached and
396+
* used again after the table has been filled. What we're trying
397+
* to avoid is using a nestloop-type plan on a table that has
398+
* grown substantially since the plan was made. Normally,
399+
* autovacuum/autoanalyze will occur once enough inserts have
400+
* happened and cause cached-plan invalidation; but that doesn't
401+
* happen instantaneously, and it won't happen at all for cases
402+
* such as temporary tables.)
398403
*
399404
* We approximate "never vacuumed" by "has relpages = 0", which
400405
* means this will also fire on genuinely empty relations.Not
401406
* great, but fortunately that's a seldom-seen case in the real
402407
* world, and it shouldn't degrade the quality of the plan too
403408
* much anyway to err in this direction.
409+
*
410+
* There are two exceptions wherein we don't apply this heuristic.
411+
* One is if the table has inheritance children. Totally empty
412+
* parent tables are quite common, so we should be willing to
413+
* believe that they are empty. Also, we don't apply the 10-page
414+
* minimum to indexes.
404415
*/
405-
if (curpages<10&&rel->rd_rel->relpages==0)
416+
if (curpages<10&&
417+
rel->rd_rel->relpages==0&&
418+
!rel->rd_rel->relhassubclass&&
419+
rel->rd_rel->relkind!=RELKIND_INDEX)
406420
curpages=10;
407421

408422
/* report estimated # pages */
@@ -418,16 +432,18 @@ estimate_rel_size(Relation rel, int32 *attr_widths,
418432
reltuples= (double)rel->rd_rel->reltuples;
419433

420434
/*
421-
* If it's an index, discount the metapage. This is a kluge
422-
* because it assumes more than it ought to about index contents;
423-
* it's reasonably OK for btrees but a bit suspect otherwise.
435+
* If it's an index, discount the metapage while estimating the
436+
* number of tuples. This is a kluge because it assumes more than
437+
* it ought to about index structure. Currently it's OK for
438+
* btree, hash, and GIN indexes but suspect for GiST indexes.
424439
*/
425440
if (rel->rd_rel->relkind==RELKIND_INDEX&&
426441
relpages>0)
427442
{
428443
curpages--;
429444
relpages--;
430445
}
446+
431447
/* estimate number of tuples from previous tuple density */
432448
if (relpages>0)
433449
density=reltuples / (double)relpages;

0 commit comments

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