For a freshly pg_restore'd 9.2 database, would VACUUM ANALYZE update statistics any better than just an ANALYZE? After a restore, we ran a bunch of ANALYZEs on each table individually using GNU 'parallel' (for speed). Many of these tables are child tables in a partition. Following the ANALYZEs, a join with the parent table showed all of the child tables scanned sequentially. After running VACUUM ANALYZE on the whole database, the same join used index-only scans on the child tables. An examination of the fine manual implies there may be some difference (or a documentation conflict?) between running ANALYZE manually on individual tables and an unqualified ANALYZE on the whole database. 5.9.6: "If you are using manual VACUUM or ANALYZE commands, don't forget that you need to run them on each partition individually. A command like: ANALYZE measurement; will only process the master table." ANALYZE: "If the table being analyzed has one or more children, ANALYZE will gather statistics twice: once on the rows of the parent table only, and a second time on the rows of the parent table with all of its children. This second set of statistics is needed when planning queries that traverse the entire inheritance tree. The autovacuum daemon, however, will only consider inserts or updates on the parent table itself when deciding whether to trigger an automatic analyze for that table. If that table is rarely inserted into or updated, the inheritance statistics will not be up to date unless you run ANALYZE manually." Can anyone explain what's going on here? Thanks, Paul Jones -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
