Thread: INSERT extremely slow with large data sets

INSERT extremely slow with large data sets

From
Slavisa Garic
Date:
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





Re: INSERT extremely slow with large data sets

From
Christoph Haller
Date:
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
>