best strategy doing a large copy and using indexes

Поиск
Список
Период
Сортировка
От Dirk Lutzebaeck
Тема best strategy doing a large copy and using indexes
Дата
Msg-id 14459.19087.735901.684613@ampato.aeccom.com
обсуждение исходный текст
Ответы Re: [SQL] best strategy doing a large copy and using indexes  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-sql
I have a table with 7 individual indexes (it models an object store
with different types) and need to copy large data lists into this
table regularly. I'm using COPY for it but it is unacceptable slow
(4min for 1000 rows on a Linux/PII-400). I'm anticipating more than
20000 rows to copy. What combination of the following options is the
best strategy to make this more efficient?

a) drop indexes before COPY and recreate them after COPY

b) put COPY in a transaction

c) do VACUUM before (or after?)

d) do VACUUM ANALYZE before (or after?)

e) COPY to temp table and then make an INSERT SELECT FROM temp table

f) anything else


I'm using -f for the backend already.

Dirk







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

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: [SQL] createdb -D xxxx not working
Следующее
От: Thomas Lockhart
Дата:
Сообщение: Re: [HACKERS] Re: [SQL] createdb -D xxxx not working