On Fri, 24 Jul 2020 at 16:01, Christophe Pettus <xof@xxxxxxxxxxxx> wrote: > I realize I've never quite known this; where does the planner get the row estimates for an empty table? Example: We just assume there are 10 pages if the relation has not yet been vacuumed or analyzed. The row estimates you see are the number of times 1 tuple is likely to fit onto a single page multiplied by the assumed 10 pages. If you had made your table wider then the planner would have assumed fewer rows There's a comment that justifies the 10 pages, which, as of master is in table_block_relation_estimate_size(). It'll be somewhere else in pg12. * HACK: if the relation has never yet been vacuumed, use a minimum size * estimate of 10 pages. The idea here is to avoid assuming a * newly-created table is really small, even if it currently is, because * that may not be true once some data gets loaded into it. Once a vacuum * or analyze cycle has been done on it, it's more reasonable to believe * the size is somewhat stable. * * (Note that this is only an issue if the plan gets cached and used again * after the table has been filled. What we're trying to avoid is using a * nestloop-type plan on a table that has grown substantially since the * plan was made. Normally, autovacuum/autoanalyze will occur once enough * inserts have happened and cause cached-plan invalidation; but that * doesn't happen instantaneously, and it won't happen at all for cases * such as temporary tables.) * * We approximate "never vacuumed" by "has relpages = 0", which means this * will also fire on genuinely empty relations. Not great, but * fortunately that's a seldom-seen case in the real world, and it * shouldn't degrade the quality of the plan too much anyway to err in * this direction. * * If the table has inheritance children, we don't apply this heuristic. * Totally empty parent tables are quite common, so we should be willing * to believe that they are empty. The code which decides if the table has been vacuumed here assumes it has not if pg_class.relpages == 0. So even if you were to manually vacuum the table the code here would think it's not yet been vacuumed. David
