pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Table maintenance: order of operations important?
3+ messages / 3 participants
[nested] [flat]

* Table maintenance: order of operations important?
@ 2004-05-20 13:48  Jeff Boes <mur@qtm.net>
  0 siblings, 1 reply; 3+ messages in thread

From: Jeff Boes @ 2004-05-20 13:48 UTC (permalink / raw)
  To: pgsql-admin

We have a number of tables from which we expire (delete) rows nightly. 
Is there any value to ordering the operations, in terms of either table 
physical organization or elapsed time?

E.g.,

   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?


-- 
It may not always be easy, convenient, or politically correct to stand
for truth and right, but it is the right thing to do. Always.
________                                          --M. Russell Ballard
Jeffery Boes <>< mur@qtm.net



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Table maintenance: order of operations important?
@ 2004-05-20 17:52  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Jeff Boes <mur@qtm.net>
  0 siblings, 1 reply; 3+ messages in thread

From: Tom Lane @ 2004-05-20 17:52 UTC (permalink / raw)
  To: Jeff Boes <mur@qtm.net>; +Cc: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Table maintenance: order of operations important?
@ 2004-05-20 18:53  Gaetano Mendola <mendola@bigfoot.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 3+ messages in thread

From: Gaetano Mendola @ 2004-05-20 18:53 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>

Tom Lane wrote:

> 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.

Without cluster with 7.4.2 a REINDEX is redundant after a VACUUM FULL ?



Regards
Gaetano Mendola





^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2004-05-20 18:53 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-05-20 13:48 Table maintenance: order of operations important? Jeff Boes <mur@qtm.net>
2004-05-20 17:52 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-05-20 18:53   ` Gaetano Mendola <mendola@bigfoot.com>

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