pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: Jochem van Dieten <jochemd@oli.tudelft.nl>
To: pgsql-performance@postgresql.org
Subject: Re: "Vacuum Full Analyze" taking so long
Date: Tue, 26 Jul 2005 11:58:17 +0200
Message-ID: <42E60939.9010905@oli.tudelft.nl> (raw)
References: <C45835824D00A844BBD0F032D5CDED920116DC12@pisgana01sxch01.ana.firstamdata.com>

Tomeh, Husam wrote:
> The other question I have. What would be the proper approach to rebuild
> indexes. I re-indexes and then run vacuum/analyze. Should I not use the
> re-index approach, and instead, drop the indexes, vacuum the tables, and
> then create the indexes, then run analyze on tables and indexes?? 

If you just want to rebuild indexes, just drop and recreate.

However, you are also running a VACUUM FULL, so I presume you 
have deleted a significant number of rows and want to recover the 
space that was in use by them. In that scenario, it is often 
better to CLUSTER the table to force a rebuild. While VACUUM FULL 
moves the tuples around inside the existing file(s), CLUSTER 
simply creates new file(s), moves all the non-deleted tuples 
there and then swaps the old and the new files. There can be a 
significant performance increase in doing so (but you obviously 
need to have some free diskspace).
If you CLUSTER your table it will be ordered by the index you 
specify. There can be a performance increase in doing so, but if 
you don't want to you can also do a no-op ALTER TABLE and change 
a column to a datatype that is the same as it already has. This 
too will force a rewrite of the table but without ordering the 
tuples.

So in short my recommendations:
- to rebuild indexes, just drop and recreate the indexes
- to rebuild everything because there is space that can 
bepermanently reclaimed, drop indexes, cluster or alter the 
table, recreate the indexes and anlyze the table

Jochem

view thread (8+ messages)  latest in thread

Message-ID: <42E60939.9010905@oli.tudelft.nl>
Permalink:  ../42E60939.9010905@oli.tudelft.nl/
Also on:    postgresql.org/message-id/42E60939.9010905@oli.tudelft.nl

copy link · copy postgr.es

reply

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: jochemd@oli.tudelft.nl
  Subject: Re: "Vacuum Full Analyze" taking so long
  In-Reply-To: <42E60939.9010905@oli.tudelft.nl>

* 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