Re: overestimate on empty table

Justin Pryzby <pryzby(at)telsasoft(dot)com> writes:
> As the queued_alters table is typically empty (and autoanalyzed with
> relpages=0), I see "why":

> ./src/backend/optimizer/util/plancat.c
> | if (curpages < 10 &&
> | rel->rd_rel->relpages == 0 &&
> | !rel->rd_rel->relhassubclass &&
> | rel->rd_rel->relkind != RELKIND_INDEX)
> | curpages = 10;

So I'm sure you read the comment above that, too.

I'm loath to abandon the principle that the planner should not believe
that tables are empty/tiny without some forcing function. There are
going to be way more people screaming about the plans they get from
too-small rowcount estimates than the reverse. However, maybe we could
do better about detecting whether a vacuum or analyze has really happened.
(Autovacuum won't normally touch a table until a fair number of rows have
been put in it, so if a table is tiny but has been vacuumed, we can
presume that that was a manual action.)

One idea is to say that relpages = reltuples = 0 is only the state that
prevails for a freshly-created table, and that VACUUM or ANALYZE should
always set relpages to at least 1 even if the physical size is zero.
Dunno if that would confuse people. Or we could bite the bullet and
add a "relanalyzed" bool flag to pg_class. It's not like that's going
to be a noticeable percentage increase in the row width ...

> But is there a better way (I don't consider adding a row of junk to be a significant improvement).

Not ATM.

regards, tom lane