Skip site navigation (1) Skip section navigation (2)

Re: strange row number estimates in pg9.1rc1

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "Sergey E(dot) Koposov" <math(at)sai(dot)msu(dot)ru>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: strange row number estimates in pg9.1rc1
Date: 2011-09-01 22:02:22
Message-ID: 201109012202.p81M2Mj13124@momjian.us (view raw or flat)
Thread:
Lists: pgsql-hackers
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. +

In response to

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2011-09-01 22:55:19
Subject: Re: pg_upgrade automatic testing
Previous:From: Daniel FarinaDate: 2011-09-01 21:44:16
Subject: Re: sha1, sha2 functions into core?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group