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 <br...@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
analyze_new_cluster.sh
Description: Bourne shell script
This script will generate minimal optimizer statistics rapidly
so your system is usable, and then gather statistics twice more
with increasing accuracy. When it is done, your system will
have the default level of optimizer statistics.
If you have used ALTER TABLE to modify the statistics target for
any tables, you might want to remove them and restore them after
running this script because they will delay fast statistics generation.
If you would like default statistics as quickly as possible, cancel
this script and run:
vacuumdb --all --analyze-only
Generating minimal optimizer statistics (1 target)
--------------------------------------------------
vacuumdb: vacuuming database "postgres"
vacuumdb: vacuuming database "template1"
vacuumdb: vacuuming database "test"
The server is now available with minimal optimizer statistics.
Query performance will be optimal once this script completes.
Generating medium optimizer statistics (10 targets)
---------------------------------------------------
vacuumdb: vacuuming database "postgres"
vacuumdb: vacuuming database "template1"
vacuumdb: vacuuming database "test"
Generating default (full) optimizer statistics (100 targets?)
-------------------------------------------------------------
vacuumdb: vacuuming database "postgres"
vacuumdb: vacuuming database "template1"
vacuumdb: vacuuming database "test"
Done
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
