Re: Optimize for insertions?

On Mon, 26 Nov 2001, Gurupartap Davis wrote:

> I'm worried about creating indexes after all the data is in there,
> though...I'm going to need 4-5 indexes. Isn't that going to take a hella
> long time? How are indexes created anyhow? I would assume a linear search
> through every record in the table...would vacuum help at all? how bout
> vacuum analyze?

I am somewhat of a newbie, but I was told that 'copy' doesn't use the
indexes so you could create your indexes and when you are all done with
the copy commands then run a vacuum analyze.

I do bulk uploads daily from a Foxpro system, been 7million my biggest
table, and I have found that a plain vacuum is helpfull after each bulk upload.
When I am done with all my bulk uploads, 3 files, then I do one vacuum analyze.

In theory you could just do a single vacuum analyze when you are all done,
but for me a vacuum after each upload seems to help. In particular I am
still trying to decide whether to use truncate or delete's. When I use
delete old the old records seems to still "be around" so the vacuum gets
rid of them. The main advantage of truncate is that it is a fast way to
empty a table, it's main drawback is that it can not be rolled back.