pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jeff Boes <mur@qtm.net>
Cc: pgsql-admin@postgresql.org
Subject: Re: Table maintenance: order of operations important?
Date: Thu, 20 May 2004 13:52:12 -0400
Message-ID: <3030.1085075532@sss.pgh.pa.us> (raw)
In-Reply-To: <fe92d61c6c9a3eea2baa0b200ea5572f@news.teranews.com>
References: <fe92d61c6c9a3eea2baa0b200ea5572f@news.teranews.com>

Jeff Boes <mur@qtm.net> writes:
>    DELETE FROM foo WHERE date_expires < now();
>    VACUUM ANALYZE foo;
>    CLUSTER foo;
>    REINDEX TABLE foo;

> How would you choose to order these (under 7.4.x) for fastest 
> turn-around? Does it matter?

If you are going to CLUSTER then the VACUUM and the REINDEX are both
utterly redundant.  The ANALYZE is still useful but should be done after
CLUSTER since its physical-order-correlation stats will be quite wrong
if done beforehand.  In other words there is only one sane way to do
this and it is

    DELETE FROM foo WHERE date_expires < now();
    CLUSTER foo;
    ANALYZE foo;

You could possibly make a case for

    DELETE FROM foo WHERE date_expires < now();
    CLUSTER foo;
    VACUUM ANALYZE foo;

The VACUUM won't do anything useful in terms of reclaiming space (there
being none to reclaim just after a CLUSTER) but it would ensure that all
rows in the table are marked as committed-good, rather than leaving that
work to be done by the first transaction that happens to hit each row.

			regards, tom lane



view thread (3+ messages)  latest in thread

Message-ID: <3030.1085075532@sss.pgh.pa.us>
Permalink:  ../3030.1085075532@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/3030.1085075532@sss.pgh.pa.us

 · 

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: tgl@sss.pgh.pa.us, mur@qtm.net
  Subject: Re: Table maintenance: order of operations important?
  In-Reply-To: <3030.1085075532@sss.pgh.pa.us>
  References: <fe92d61c6c9a3eea2baa0b200ea5572f@news.teranews.com> <3030.1085075532@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