From: Tom Lane <tgl@sss.pgh.pa.us>
To: Steve Oualline <soualline@stbernard.com>
Cc: pgsql-performance@postgresql.org, "Jiu Zheng" <Jiu@stbernard.com>
Subject: Re: VACUUM Performance
Date: Sat, 21 Oct 2006 01:02:06 -0400
Message-ID: <21641.1161406926@sss.pgh.pa.us> (raw)
In-Reply-To: <00C5463E8A5F7C41A8396D701A6734B1527A62@mail01.stbernard.com>
References: <00C5463E8A5F7C41A8396D701A6734B1527A62@mail01.stbernard.com>
"Steve Oualline" <soualline@stbernard.com> writes:
> Question: I have a big table with 120,000,000 records.
> Let's assume that I DELETE 4,000,000 records, VACUUM FULL, and REINDEX.
> Now I have the same table, but with 240,000,000 records.
> I DELETE 8,000,000 records, VACUUM FULL, and REINDEX.
> Should the second operation with twice the data take twice the time as =
> the first?
At least. If you intend to reindex all the indexes, consider instead
doing
DROP INDEX(es)
VACUUM FULL
re-create indexes
as this avoids the very large amount of effort that VACUUM FULL puts
into index maintenance --- effort that's utterly wasted if you then
reindex.
CLUSTER and some forms of ALTER TABLE can accomplish a table rewrite
with less hassle than the above, although strictly speaking they violate
MVCC by discarding recently-dead tuples.
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, soualline@stbernard.com, Jiu@stbernard.com
Subject: Re: VACUUM Performance
In-Reply-To: <21641.1161406926@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