X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id DD619D1CE3C for ; Thu, 20 May 2004 14:59:03 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 55414-01 for ; Thu, 20 May 2004 14:58:41 -0300 (ADT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id B5CF2D1CCC5 for ; Thu, 20 May 2004 14:52:12 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i4KHqD52003031; Thu, 20 May 2004 13:52:13 -0400 (EDT) To: Jeff Boes Cc: pgsql-admin@postgresql.org Subject: Re: Table maintenance: order of operations important? In-reply-to: References: Comments: In-reply-to Jeff Boes message dated "Thu, 20 May 2004 13:48:34 +0000" Date: Thu, 20 May 2004 13:52:12 -0400 Message-ID: <3030.1085075532@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200405/216 X-Sequence-Number: 13532 Jeff Boes 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