Re: Restore v. Running COPY/INDEX seperatly

On Sun, 26 Aug 2007, Benjamin Arai wrote:

> Hi,
>
> So, I built my tables which contains a TSearch2 field by
>
> 1. Create table without indexes
> 2. COPY data into table
> 3. ALTER TABLE tblMessages ADD COLUMN idxFTI tsvector;
> 4. UPDATE tblMessages SET idxFTI=to_tsvector('default', strMessage);

vacuum here

> 5. Index all the fields including the TSearch2 field
>
> The process takes several days.
>
> In contrast, if I backup the table and restore it to a new table it takes a
> fraction of the time as running the above operation manually. I am building
> my indexes at the end but I think the step 4 may be causing uneeded overhead.
> Can I somehow just copy data into the idxFTI field during the copy process?
> Is there anything else I can do to get my loading process to perform similar
> to backup/restore?
>
> Does pg_dump also dump the indexes? That would explain why it is so much
> faster...
>
> Benjamin
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru)
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83