Impact of existing indexes on performance of COPY FROM

indexing postgresql sqlbulkcopy

Question

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?

Accepted Answer

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.


Popular Answer

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.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why