Re: pg_upgrade and statistics

On Thu, Mar 15, 2012 at 11:15:42AM -0400, Andrew Dunstan wrote:
> You're not the only person who could do that. I don't think this is
> all down to you. It should just be understood that if the stats
> format is changed, adjusting pg_upgrade needs to be part of the
> change. When we modified how enums worked, we adjusted pg_upgrade at
> the same time. That sort of thing seems totally reasonable to me.
>
> I haven't looked at it, but I'm wondering how hard it is going to be
> in practice?

Well, the reason I am not recommending migration is because the work
will _not_ fall on me, but rather on the larger community of developers,
who might or might not care about pg_upgrade.  (I am concerned about
pg_upgrade retarding development progress.)

We are already telling developers not to change the binary storage
format without considering pg_upgrade --- do we want to do the same for
optimizer statistics?  Does the optimizer statistics format change more
frequently than the storage format?  I think the answer is yes.  Does it
change too frequently to require migration work?  I don't know the
answer to that, partly because I would not be the one doing the work.

Also, considering we are on the last 9.2 commit-fest, I doubt we can get
something working for statistics migration for 9.2, I think the
incremental statistics build approach is our only way to improve this
for 9.2, and frankly, 9.1 users can also use the script once I post it.

FYI, I have not received a huge number of complaints about the old
analyze recommendation --- a few, but not a flood.  The incremental
build approach might be good enough.

My wild guess is that even if we migrated all statistics, the migrated
statistics will still not have any improvements we have made in
statistics gathering, meaning there will still be some kind of analyze
process necessary, hopefully just on the affected tables --- that would
be shorter, but perhaps not shorter enough to warrant the work in
migrating the statistics.

I am attaching the updated script and script output.

Please, don't think I am ungrateful for the offers of help in migrating
statistics.  I just remember how complex the discussion was when we
modified the enum improvements to allow pg_upgrade, and how complex the
checksum discussion was related to pg_upgrade.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

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