Jeff Boes <jboes@nexcerpt.com> writes:
> What would cause an otherwise well-behaved table to start doing this? Is
> this just a "dead spot" in the ANALYZE command? (By which I mean:
> ANALYZE randomly sampling rows, but my data is not terribly random, so
> it gets fooled?)
7.4's ANALYZE is indeed easily fooled by nonuniform tuple density.
8.0 uses a different sampling algorithm that we hope will do better.
> [And here's the remaining question in my puzzled mind: ANALYZE would not
> change the reltuples value, but VACUUM FULL ANALYZE did. Er-wha?]
Any variant of VACUUM records the exact number of tuples that it found
in its full table scan in reltuples. It's only a standalone ANALYZE
that has to approximate.
The case where you saw reltuples > count(*) probably came from the fact
that VACUUM counts physical tuples --- that is, the difference reflects
dead-but-not-yet-removable tuples.
regards, tom lane