From: Fernando Hevia <fhevia@ip-tel.com.ar>
To: 'Gregory Stark' <stark@enterprisedb.com>
To: 'Bill Moran' <wmoran@collaborativefusion.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Big delete on big table... now what?
Date: Mon, 25 Aug 2008 10:38:08 -0300
Message-ID: <04cd01c906b7$cfa0bf10$8f01010a@iptel.com.ar> (raw)
In-Reply-To: <87r68gpns3.fsf@oxford.xeocode.com>
References: <042c01c90496$ca8725f0$8f01010a@iptel.com.ar>
<20080822184407.37155367.wmoran@collaborativefusion.com>
<87r68gpns3.fsf@oxford.xeocode.com>
> Gregory Stark <start@enterprisedb.com> writes:
>
> "Bill Moran" <wmoran@collaborativefusion.com> writes:
>
> > "Fernando Hevia" <fhevia@ip-tel.com.ar> wrote:
> >> Hi list.
> >> I have a table with over 30 million rows. Performance was dropping
> >> steadily so I moved old data not needed online to an
> historic table.
> >> Now the table has about 14 million rows. I don't need the
> disk space
> >> returned to the OS but I do need to improve performance.
> Will a plain
> >> vacuum do or is a vacuum full necessary?
> >> ¿Would a vacuum full improve performance at all?
> >
> > If you can afford the downtime on that table, cluster would be best.
> >
> > If not, do the normal vacuum and analyze. This is unlikely
> to improve
> > the performance much (although it may shrink the table _some_) but
> > regular vacuum will keep performance from getting any worse.
>
> Note that CLUSTER requires enough space to store the new and
> the old copies of the table simultaneously. That's the main
> reason for VACUUM FULL to still exist.
>
> There is also the option of doing something like (assuming id
> is already an integer -- ie this doesn't actually change the data):
>
> ALTER TABLE x ALTER id TYPE integer USING id;
>
> which will rewrite the whole table. This is effectively the
> same as CLUSTER except it doesn't order the table according
> to an index. It will still require enough space to hold two
> copies of the table but it will be significantly faster.
>
Yes, I can afford a downtime on Sunday.
Actually the clustering option would help since most of our slow queries use
the same index.
Thanks Bill and Gregory for the advice.
Regards,
Fernando.
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: fhevia@ip-tel.com.ar, stark@enterprisedb.com, wmoran@collaborativefusion.com
Subject: Re: Big delete on big table... now what?
In-Reply-To: <04cd01c906b7$cfa0bf10$8f01010a@iptel.com.ar>
* 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