Restore v. Running COPY/INDEX seperatly

Поиск
Список
Период
Сортировка
От Benjamin Arai
Тема Restore v. Running COPY/INDEX seperatly
Дата
Msg-id 4825E439-3559-465A-8719-666E75872B91@benjaminarai.com
обсуждение исходный текст
Ответы Re: Restore v. Running COPY/INDEX seperatly
Список 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

В списке pgsql-general по дате отправления:

Предыдущее
От: "Merlin Moncure"
Дата:
Сообщение: Re: problem Linking a TTable component to a pgsql view using BCB5
Следующее
От: David Fetter
Дата:
Сообщение: Re: Bigtime scaling of Postgresql (cluster and stuff I suppose)