Re: postgres performance: comparing 2 data centers

"Michael Nonemacher" <Michael_Nonemacher(at)messageone(dot)com> writes:
> It seems like the statistics are wildly different depending on whether
> the last operation on the table was a 'vacuum analyze' or an 'analyze'.
> Vacuum or vacuum-analyze puts the correct number (~500k) in
> pg_class.reltuples, but analyze puts 7000 in pg_class.reltuples.

Okay, this is a known issue: in 7.4 and earlier, ANALYZE is easily
fooled as to the total number of rows in the table. It samples the
initial portion of the table and assumes that the density of live rows
per page in that section is representative of the rest of the table.
Evidently that assumption is way off for your table. There's an
improved sampling algorithm in CVS tip that we hope will avoid this
error in 7.5 and beyond, but the immediate problem for you is what
to do in 7.4. I'd suggest either VACUUM FULL or CLUSTER to clean out
the existing dead space; then you should look into whether you need
to increase your vacuum frequency and/or FSM settings to keep it from
getting into this state again. Ideally the average dead space per
page *should* be consistent over the whole table, and the fact that
it isn't suggests strongly that you've got space-management issues
to deal with.

regards, tom lane