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);
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
