From: | Andres Freund <andres(at)anarazel(dot)de> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Why not represent "never vacuumed" accurately wrt pg_class.relpages? |
Date: | 2018-12-11 18:51:05 |
Message-ID: | 20181211185105.nok3q7yxntcd3dga@alap3.anarazel.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi,
On 2018-12-11 13:43:47 -0500, Tom Lane wrote:
> Andres Freund <andres(at)anarazel(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2018-12-11 18:59:55 | Re: Why not represent "never vacuumed" accurately wrt pg_class.relpages? |
Previous Message | Tom Lane | 2018-12-11 18:43:47 | Re: Why not represent "never vacuumed" accurately wrt pg_class.relpages? |