Thread: Stats update difference between VACUUM ANALYZE and ANALYZE in 9.2?

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

Paul Jones <pbj@cmicdo.com> writes:
> For a freshly pg_restore'd 9.2 database, would VACUUM ANALYZE update
> statistics any better than just an ANALYZE?

Not as far as the statistics kept in pg_stat go.

> 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.

VACUUM would have caused the page-all-visible flags to get set for all
pages of unchanging tables.  I don't recall whether ANALYZE has any side
effects on those flags at all, but it certainly would not have set them
for pages it didn't even visit, which would be most.

Net result is that the pg_class.relallvisible fractions didn't get high
enough to persuade the planner that index-only scans would be effective.
I guess you could call that a statistic, but it's really about the
contents of the tables' free space maps.

Another possible theory is that you skipped ANALYZE'ing the partition
parent tables in your first pass, but I'm betting on the all-visible
fractions as being the issue.

            regards, tom lane