X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 9D2F29DCBF4
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 28 Feb 2006 08:10:36 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 89067-07
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 28 Feb 2006 08:10:39 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from bilbao.pandasoftware.es (unknown [62.14.249.71])
	by postgresql.org (Postfix) with ESMTP id EE2FA9DCBDD
	for <pgsql-performance@postgresql.org>;
	Tue, 28 Feb 2006 08:10:33 -0400 (AST)
Received: from escorpexh05.pandasoftware.local (escorpext02 [192.168.100.182])
	by bilbao.pandasoftware.es (Postfix) with ESMTP id D03D81B3748
	for <pgsql-performance@postgresql.org>;
	Tue, 28 Feb 2006 13:10:03 +0100 (CET)
Received: from ESCORPEXH03.pandasoftware.local ([172.16.0.70]) by
	escorpexh05.pandasoftware.local with Microsoft
	SMTPSVC(6.0.3790.211); Tue, 28 Feb 2006 13:10:21 +0100
Received: escorpexh03.pandasoftware.local 172.16.0.73 from 172.16.3.16
	172.16.3.16 via HTTP with MS-WebStorage 6.5.6944
Received: from pndsoft by escorpexh03.pandasoftware.local;
	28 Feb 2006 13:11:05 +0100
Subject: vacuum, analyze and reindex
From: Javier Somoza <jsomoza@pandasoftware.es>
To: pgsql-performance@postgresql.org
Content-Type: multipart/alternative; boundary="=-QO1J/z6tDBQ7dsbbZ1QI"
Message-Id: <1141128665.1555.33.camel@pndsoft>
Mime-Version: 1.0
X-Mailer: Ximian Evolution 1.4.6 
Date: Tue, 28 Feb 2006 13:11:05 +0100
X-OriginalArrivalTime: 28 Feb 2006 12:10:21.0236 (UTC)
	FILETIME=[F25CB740:01C63C5F]
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=1.134 required=5 tests=[AWL=1.132,
	HTML_MESSAGE=0.001, UNPARSEABLE_RELAY=0.001]
X-Spam-Score: 1.134
X-Spam-Level: *
X-Archive-Number: 200602/429
X-Sequence-Number: 17415


--=-QO1J/z6tDBQ7dsbbZ1QI
Content-Type: text/plain
Content-Transfer-Encoding: 7bit



            Hi all

            I've a question about vacuuming, ...

            Vacuum:  cleans out obsolete and deleted registers...
            Analyze:  update statistics for the planner
            Reindex:  rebuild indexes

            I think the correct order to perform the database
maintenance for performance is:

            1 - Vacuum
            2 - Reindex
            3 - Analyze

            So the planner is updated with the updated indexes.


            The autovacuum daemon does vacuum and analyze. Not reindex.
            So, no way to perform it in that order.

            What do you think?
            How often do you reindex your tables?

            Thx all

--=-QO1J/z6tDBQ7dsbbZ1QI
Content-Type: text/html; charset=utf-8

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 TRANSITIONAL//EN">
<HTML>
<HEAD>
  <META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=UTF-8">
  <META NAME="GENERATOR" CONTENT="GtkHTML/3.0.10">
</HEAD>
<BODY>
<BR>
<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Hi all<BR>
<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; I've a question about vacuuming, ...<BR>
<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Vacuum:  cleans out obsolete and deleted registers...<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Analyze:&nbsp; update statistics for the planner<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Reindex:&nbsp; rebuild indexes<BR>
<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; I think the correct order to perform the database maintenance for performance is:<BR>
<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1 - Vacuum<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2 - Reindex<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3 - Analyze<BR>
<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; So the planner is updated with the updated indexes.<BR>
<BR>
<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; The autovacuum daemon does vacuum and analyze. Not reindex.<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; So, no way to perform it in that order.<BR>
<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; What do you think?<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; How often do you reindex your tables?<BR>
<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Thx all
</BODY>
</HTML>

--=-QO1J/z6tDBQ7dsbbZ1QI--