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
>