Restore v. Running COPY/INDEX seperatly

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

Responses

Browse pgsql-general by date

  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