Re: Vacuuming

Paul Lambert <paul(dot)lambert(at)autoledgers(dot)com(dot)au> writes:
> Tom Lane wrote:
>> What you *do* want to do in this situation is an ANALYZE.

> Should the ANALYZE be done before or after indexes are built? Or is that
> irrelevant?

For ordinary indexes it doesn't matter. If you have any expression
indexes then you should build them before running ANALYZE, because
ANALYZE takes the hint to collect stats on those expressions as well
as the raw column values. (Eventually this advice might apply to
multicolumn and partial indexes as well, but right now ANALYZE doesn't
treat those specially, AFAIR.) In any case there's no good reason
to do ANALYZE first if you have a free choice.

> Should I not even bother rebuilding indexes when I do these loads?

There's some value in the advice to "drop indexes, load data, recreate
indexes". TRUNCATE will happily truncate the indexes to nothing along
with the table, but when you then load data you are building the indexes
incrementally instead of in-bulk. This process is slower than a bulk
index build and ends up with a more-fragmented index. (At least for
btree indexes --- I'm not sure which other index types are smarter
about bulk vs incremental build.)

> Currently I:
> 1) Drop Indexes
> 2) Truncate and copy in new data
> 3) Vacuum - now changed to analyze.
> 4) Create indexes

I'd interchange steps 3 and 4; otherwise you are good.

regards, tom lane