Hi,
I'm just testing insertion of about 600 000 records inside 3 tables.
Just making a big text file with 3 inserts each time (for my 3 tables) like
insert into xx (yy) values ('data'); so I have 3 x 600 000 inserts inside the
file.
Table N°2 have a reference on the Table N°1 with the primary key ...
It's not a transaction ... I have only a primary key on each 3 tables ...
The tables where empty at the beginning and all the database vacuum full
analysed before the test.
Same result on PostgreSQL 7.2.3 and 7.3.2 ...
So I get about 1000 inserts done in 10 seconds at the beginning of the file,
then after one hour I get 25 seconds for 1000 inserts ... and it's going
slower and slower ...
Before making a text file ... I have done the same thing with a Perl script
with DBI to make the 3 inserts in a transaction (of the 600 000 records x3),
and it was really slower (I stopped the script after 10 hours and 819 000
inserts done.) And same thing it was going slower and slower ... it was about
2000 inserts for 10 seconds the 10 first minutes... then after 10 hours I was
at 40 inserts for 10 seconds ...
I'm using for my tests as server with Bi-Pentium III 1.8 Ghz - 2Mb of RAM and
Linux 2.4.18 ... the memory do not move ... the swap was never used ...
Why PostgreSQL's going slower ?
Do you have any idea to have a better and quicker insert method ?
Thanks per advance for your help :o)
regards,
--
Hervé