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@xxxxxxxxxx, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
