From: Tom Lane <tgl@sss.pgh.pa.us>
To: Michael Nonemacher <Michael_Nonemacher@messageone.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: postgres performance: comparing 2 data centers
Date: Sat, 05 Jun 2004 00:17:35 -0400
Message-ID: <14802.1086409055@sss.pgh.pa.us> (raw)
In-Reply-To: <E3A41572DB871B42AB6939873D95E8CA03874D@auscorpex-1.austin.messageone.com>
References: <E3A41572DB871B42AB6939873D95E8CA03874D@auscorpex-1.austin.messageone.com>
"Michael Nonemacher" <Michael_Nonemacher@messageone.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, Michael_Nonemacher@messageone.com
Subject: Re: postgres performance: comparing 2 data centers
In-Reply-To: <14802.1086409055@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox