Re: strange row number estimates in pg9.1rc1

Tom Lane wrote:
> I wrote:
> > "Sergey E. Koposov" <math(at)sai(dot)msu(dot)ru> writes:
> >> I'm seeing something weird which looks like a bug in 9.1rc1 after the
> >> upgrade 8.4->9.0->9.1 done using pg_upgrade.
>
> > Hm, I wonder what pg_upgrade left relpages/reltuples set to ...
>
> Sure enough, that's the problem. pg_upgrade leaves relpages/reltuples
> set to zero, but it also imports the visibility map pages from the old
> cluster. If the old visibility map shows the table as all-visible,
> then this happens when you try to VACUUM ANALYZE the table:
>
> 1. VACUUM doesn't process any pages, so it has no tuple density
> estimate. It leaves reltuples set to zero, but it does set relpages.
>
> 2. ANALYZE scans some part of the table. It gets a tuple density
> estimate for those pages ... but if that's only a small fraction of
> the table, it believes the zero estimate of tuple density elsewhere.
> So you get only a small update of reltuples.
>
> (The above behavior is new as of commit
> b4b6923e03f4d29636a94f6f4cc2f5cf6298b8c8, BTW.)
>
> Basically, step 1 is buggy here: if we aren't making an update to
> reltuples, we shouldn't set relpages either. Setting it nonzero
> changes the implied tuple density from "unknown" to "known zero",
> which is wrong.
>
> I'll go fix that, but I think it might be a good idea for pg_upgrade
> to think about preserving the relpages/reltuples columns ...

pg_upgrade currently only restores some oids and frozenxids. We would
need to modify pg_dump --binary-upgrade mode to restore those values ---
it isn't hard to do.

> PS: right now, you cannot reproduce this in a 9.0 -> HEAD upgrade,
> because of this patch:
>
> commit 00a7c9014a8fbb7388a807daeba3e0a85b49a747
> Author: Bruce Momjian <bruce(at)momjian(dot)us>
> Date: Fri Aug 19 11:20:30 2011 -0400
>
> In pg_upgrade, don't copy visibility map files from clusters that did not
> have crash-safe visibility maps to clusters that expect crash-safety.
>
> Request from Robert Haas.
>
> I did reproduce it in a 9.0->9.1 test.

Right, that is expected.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +