Re: VACUUM FULL versus CLUSTER ON

Csaba Nagy <nagy(at)ecircle-ag(dot)com> writes:

> I won't know for sure, but I guess the least downtime you would get by
> not dropping the indexes before the delete, but do a reindex after it.
> Then cluster on the primary key...
>
> My reasoning (correct me if I'm wrong): the deletion speed won't be
> affected by the indexes, I think deletions don't touch the indexes at
> all.

That's true, more or less. I think there's a small hit actually as queries set
the hint bit and the pages have to be flushed.

As long as you're just deleting and not inserting or updating

> The REINDEX command recreates all indexes at once, I think it needs
> only one full table scan.

No, each index build has to do its own full scan. It wouldn't save much
anyways not to, where would you store the tuples in the meantime? And why
would this temporary storage place be any faster than scanning the original
table?

--
greg