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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
