Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In this answer, Erwin Brandstetter recommends

It is also substantially faster to delete indexes before a huge bulk INSERT / COPY and recreate them afterwards as it is much more costly to incrementally adjust indexes for every inserted row than to create them at once.

1) Is this true even when the table is already large? If not, is there an easy way to estimate the point of diminishing return? For example, I'm doing COPY of 30,000 records at a time into a table which already has say 100 million rows. Each batch is relatively small, but on the other hand the total batches on hand to import at the moment will more than double the size of the table.

2) If I were to delete the index before COPY, would recreating it be faster if the COPY is done with records already in index order?

There is only one index on the table, which is a 5-column primary key.

I'm doing this on a non-production machine, and I've already disabled fsync, syncronous_commit, autovacuum, etc. I'm executing the COPY operation from four separate processes concurrently on an 8-core machine. I've got 12GB of memory, so I could set maintenance_work_mem to 1GB or more if it would help.

share|improve this question
add comment

migrated from stackoverflow.com Aug 1 '13 at 1:46

This question came from our site for professional and enthusiast programmers.

1 Answer

My guess would be going with some benchmarking, measuring the time it's taking you to recreate the full index, the time it takes to insert 10k rows without index, with index. then I'd try with 1000 rows, 5000 rows, 10000 rows, to try to figure out a trend.

Because even if you try to reduce the amount of factors that could affect the operation time (as Auto-Vacuum) it still depends on your hardware and your whole postgres.conf

BONUS: if you share with us some benchmarks, we could gladly help you to figure out the trend.

share|improve this answer
    
I was afraid that would be the answer. I was looking for a quick estimation so I can just get the import done reasonably quickly. I expect that in the future I will only need to COPY a few hundred thousand records at a time. –  Aryeh Leib Taurog Aug 1 '13 at 6:10
    
Hopefully, somebody will come with a math equation. –  SDReyes Aug 1 '13 at 6:34
    
I went ahead and loaded all the data in a single transaction with no index, first doing TRUNCATE. Loading the data was much faster than the network transfer. I'll reproduce some meaningful times when I can. Rebuilding the index afterwards took about 30 minutes, with 170m records. I set maintenance_work_mem up to 9G but it only used a little over 3G. VACUUM ANALYZE on the table took about 15 minutes (!) after that. Next time I import records I should be able to provide times for COPY operations with the index. –  Aryeh Leib Taurog Aug 7 '13 at 16:20
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.