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


Fri, 14 Nov 2003 18:36:41 +1100 (EST)
Slavisa Garic <Slavisa.Garic@infotech.monash.edu.au>
INSERT extremely slow with large data sets
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




Fri, 14 Nov 2003 19:10:38 +1100 (EST)
Slavisa Garic <Slavisa.Garic@infotech.monash.edu.au>
INSERT extremely slow with large data sets
On Fri, Nov 14, 2003 at 06:36:41PM +1100, Slavisa Garic wrote:

> 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

> [too slow]

Ok, so inserting 60000 rows seems to take 0.09 seconds, and inserting
5000 takes only 0.01.  And your problem is exactly what?

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Coge la flor que hoy nace alegre, ufana. ï¿½Quiï¿½n sabe si nacera otra maï¿½ana?"

Fri, 14 Nov 2003 15:19:46 -0300
Alvaro Herrera <alvherre@dcc.uchile.cl>
Re: INSERT extremely slow with large data sets


On Fri, 14 Nov 2003, Alvaro Herrera wrote:

> On Fri, Nov 14, 2003 at 06:36:41PM +1100, Slavisa Garic wrote:
> 
> > 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
> 
> > [too slow]
> 
> Ok, so inserting 60000 rows seems to take 0.09 seconds, and inserting
> 5000 takes only 0.01.  And your problem is exactly what?

You didn't understand the question. Inserting ONE ROW when there are already
5000 ROWS present takes 0.01 seconds. Inserting ONE ROW when there are
already 60000 ROWS present takes 0.09 secods. In other words in takes
about 9 times more time to insert ONE ROW when there is a larger set of
data already in the database. As my experiments will grow and more data
will be inserted this is getting to take too long. Inserting 70000 rows
takes about just over an hour. INserting 5000 takes about minute and an
half.

I don't know if this the behaviour to be expected so that is why i posted
the question and that is my problem. I also wanted to know what can be
done to improve this if it can be,

Regards,
Slavisa
 
> -- 
> Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
> "Coge la flor que hoy nace alegre, ufana. �Qui�n sabe si nacera otra ma�ana?"
> 


Sat, 15 Nov 2003 12:03:22 +1100 (EST)
Slavisa Garic <Slavisa.Garic@infotech.monash.edu.au>
Re: INSERT extremely slow with large data sets
Slavisa Garic <Slavisa.Garic@infotech.monash.edu.au> writes:
> You didn't understand the question. Inserting ONE ROW when there are already
> 5000 ROWS present takes 0.01 seconds. Inserting ONE ROW when there are
> already 60000 ROWS present takes 0.09 secods.

The numbers you presented didn't really offer any strong grounds for
believing that there's an O(N) growth rate --- as far as I can see your
results are only barely out of the measurement-noise category.  Can you
run some tests where the issue is not in the least significant digit
of the available numbers?

But assuming for the moment that you've got hold of a real problem...

The actual insertion of a row should be essentially a constant-time
operation, since we just stick it into the last page of the table
(or any page with sufficient free space).  Insertion of index entries
for the row would have cost that depends on the number of existing
table entries, but for btree indexes I'd expect the cost to vary as
O(log2(N)) not O(N).  I do not think you've presented enough evidence
to prove that you're seeing linear rather than log-N cost growth.

Most of the serious insertion-cost problems we've seen lately have
to do with the costs of checking foreign key references ... but those
normally vary with the size of the referenced table, not the table into
which you're inserting.  Besides which you mentioned nothing about
foreign keys ... or any other schema details as far as I saw ...

			regards, tom lane

Sat, 15 Nov 2003 00:27:13 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: INSERT extremely slow with large data sets 