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.

So if I insert a single row into a postgres database it takes 18 ms. If I do this in a loop like this:

INSERT INTO contacts (numbers)
SELECT  distinct array[
        (random() * 99999999)::integer,
        (random() * 99999999)::integer
    ]
  FROM generate_series(1,4000000) AS x(id);

And I vary the number of rows inserted, the time is nonlinear. Here is the data:

-1 record - 18 ms
-20k records - 36 seconds
-50k records - 151 seconds
-100k records - 750 seconds

Why is this getting exponentially bigger? I need 10 Million records in my database for load testing and it seems to be faster to insert 50k rows and then reinsert the 50k again since 151 + 151 < 750

Any insight on this topic would be appreciated. I assume it is because postgres saves data to rollback in case the query critically fails or is cancelled by the user and postgres does not want to "half insert" the total request.

share|improve this question
    
Perhaps the 'distinct' part is causing an external sort/unique which is O(n*logn)? How does the performance look without the 'distinct'? –  Joel Nelson May 1 '13 at 19:27
    
I can't stop the query right now to check. I dont think it is the distinct but i will check when this query is finished –  Jonathan O May 1 '13 at 19:28
1  
What are the indexes you have on that table? Show the output of => \d contacts –  Clodoaldo May 1 '13 at 19:38
2  
Index rebuild operations are not linear. The bigger the index - the more time it takes to rebuild it after every INSERT. –  Igor Romanchenko May 1 '13 at 19:50
1  
Besides dropping the indexes (which are almost certainly the source of slowness, especially if you have a unique index or PK that must be tested for every new row), you might consider if you can use COPY in lieu of INSERT, if that makes sense for the real problem compared to the INSERT...SELECT example. –  Andrew Lazarus May 2 '13 at 1:19
show 4 more comments

migrated from stackoverflow.com May 2 '13 at 10:37

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

1 Answer

Leaving aside the fact that the DISTINCT is causing some weird behavior, there are two main reasons why insert times get longer as bulk loads get larger:

  1. B-tree indexes get less efficient to update as they get larger and have more tree levels. So indexes take longer to insert a the millionth value than they did the 10th.
  2. At certain sizes, you exceed certain thresholds which cause extra IO on the system, resulting in lag while the IO takes place. These thresholds, which interact in complex ways, include:
    • the size of the WAL, causing log rotation
    • the size of the RAID cache, dropping to disk speeds
    • the size of Postgres' dedicated cache, causing flushing to the FS
    • the size of the FS cache's dirty block flushing threshold
    • the size of the entire FS cache, causing emergency flushing
share|improve this answer
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.