Re: Restore v. Running COPY/INDEX seperatly

Why is a trigger faster than doing a ALTER after table is created? I
thought a trigger would be slower because it would be invoked every
iteration (a new row is inserted) during the COPY process.

Benjamin

On Aug 26, 2007, at 8:43 PM, Tom Lane wrote:

> Gregory Stark <stark(at)enterprisedb(dot)com> writes:
>>> On Sun, 26 Aug 2007, Benjamin Arai wrote:
>>>> 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);
>
>> Or you could set up a trigger to generate the tsvector when you first
>> load the data instead of adding it later.
>
> You're going to want such a trigger anyway, so installing it before
> the
> COPY step seems like the Obviously Right Thing. Any other approach
> implies rewriting the entire table after you've loaded it, with no
> compensating advantage that I can see.
>
> regards, tom lane
>