Hi,
On 2018-12-11 13:43:47 -0500, Tom Lane wrote:
> Andres Freund <andres@anarazel.de> writes:
> > On 2018-12-11 09:47:38 -0500, Tom Lane wrote:
> >> And why do you blame it on this representation? We don't believe that
> >> relpages is the actual size of the table.
>
> > No, but we assume that there's 10 pages. Even if both relpages and the
> > actual relation stats say there's not. And we assume there's as many
> > tuples on the page as can fit on it, using get_rel_data_width(). So if
> > you have a small table with a handful of entries at most, you suddenly
> > get estimates of a few hundred to ~a thousand rows.
>
> That's intentional, and not particularly constrained by the representation
> used in pg_class. The downsides of incorrectly assuming a table is tiny
> are a lot worse than those of assuming the opposite.
How's being unable to distinguish "never vacuumed" from "table is
knowingly empty right now" not constrained by the representation? I
mean:
* 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.
*
* There are two exceptions wherein we don't apply this heuristic.
* One is if the table has inheritance children. Totally empty
* parent tables are quite common, so we should be willing to
* believe that they are empty. Also, we don't apply the 10-page
* minimum to indexes.
My case of small tables of ephemeral data aside, with hash & range
partitioning it's becoming more common to have individual tables be
empty too, by virtue of nothing falling into the range/being hashed of
the partitions.
Without having a separate "no page, but really" value, how can we fix
this?
Greetings,
Andres Freund