From: | Benjamin Arai <me(at)benjaminarai(dot)com> |
---|---|
To: | PostgreSQL <pgsql-general(at)postgresql(dot)org> |
Subject: | Restore v. Running COPY/INDEX seperatly |
Date: | 2007-08-26 22:04:49 |
Message-ID: | 4825E439-3559-465A-8719-666E75872B91@benjaminarai.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | David Fetter | 2007-08-26 22:09:41 | Re: Bigtime scaling of Postgresql (cluster and stuff I suppose) |
Previous Message | Merlin Moncure | 2007-08-26 19:30:11 | Re: problem Linking a TTable component to a pgsql view using BCB5 |