Re: INSERT extremely slow with large data sets

There is a pgsql-performance list, which was created for questions like yours. 
Your problem was brought up many times before, so searching the archives is 
an alternative. 

Regards, Christoph 

> 
> Hi Everyone,
>  
> This is my first post here so please tell me to go somewhere else if this
> is the wrong place to post questions like this.
>  
> I am using PostgreSQL 7.3.2 and have used earlier versions (7.1.x onwards) 
> and with all of them I noticed same problem with INSERTs when there is a
> large data set. Just to so you guys can compare time it takes to insert
> one row into a table when there are only few rows present and when there
> are thousands:
> 
> Rows Present        Start Time        Finish Time
> ------------------------------------------------------------
> 100            1068790804.12           1068790804.12
> 1000            1068790807.87          1068790807.87
> 5000            1068790839.26        1068790839.27
> 10000            1068790909.24          1068790909.26
> 20000            1068791172.82        1068791172.85
> 30000            1068791664.06        1068791664.09 
> 40000            1068792369.94        1068792370.0
> 50000            1068793317.53        1068793317.6
> 60000            1068794369.38        1068794369.47
> 
> As you can see if takes awfully lots of time for me just to have those
> values inserted. Now to make a picture a bit clearer for you this table 
> has lots of information in there, about 25 columns. Also there are few
> indexes that I created so that the process of selecting values from there
> is faster which by the way works fine. Selecting anything takes under 5
> seconds.
> 
> Any help would be greatly appreciated even pointing me in the right
> direction where to ask this question. By the way I designed the database
> this way as my application that uses PGSQL a lot during the execution so
> there was a huge need for fast SELECTs. Our experiments are getting larger
> and larger every day so fast inserts would be good as well.
> 
> Just to note those times above are of INSERTs only. Nothing else done that
> would be included in those times. Machine was also free and that was the
> only process running all the time and the machine was Intel(R) Pentium(R)
> 4 CPU 2.40GHz.
> 
> Regards,
> Slavisa
>