Paul Lambert <paul.lambert@xxxxxxxxxxxxxxxxxx> 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
