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

Paul Jones <pbj(at)cmicdo(dot)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