After reading the PostgreSQL reference doc on COPY, I did not find how COPY deals with indexes. What exactly happens when COPYing data from a file into a table when the table already has indexes. I wonder if these indexes will be destroyed automatically first and then re-created after the loading or not?
Asked
Active
Viewed 538 times
0
Erwin Brandstetter
- 605,456
- 145
- 1,078
- 1,228
Fopa Léon Constantin
- 11,863
- 8
- 48
- 82
2 Answers
3
There is another page in the manual called Populating a Database that deals with that question. Its advice:
If you are loading a freshly created table, the fastest method is to create the table, bulk load the table's data using
COPY, then create any indexes needed for the table. Creating an index on pre-existing data is quicker than updating it incrementally as each row is loaded.
All clear, right? But read the page, there's probably more for you.
Erwin Brandstetter
- 605,456
- 145
- 1,078
- 1,228
3
COPY currently does nothing special with indexes.
If you must insert into an indexed table, there is not much of a performance difference between COPY and a batched INSERT.
So if you can't drop the indexes then just use whichever command is most convenient given the current format of the data.
jjanes
- 37,812
- 5
- 27
- 34
-
does indexes will be update to take into account the newly added rows ? – Fopa Léon Constantin Apr 04 '14 at 00:35
-
@FopaLéonConstantin Yes, of course they do. That's why it's slower. – Craig Ringer Apr 04 '14 at 01:24