Hello,
I have the following scenario:
The application read one record from a file, which contains 100 000 records.
The application checks on different conditions if this record is allready
save in the table 'test'.
If this record exists then the application manipulates record and updates
the record with UPDATE in PostgreSQL. This UPDATE affects allways 50
columns and PostgreSQL uses an index.
If this record doesn`t exit the application manipulate the record and makes
an INSERT.
Then it reads the next record from the file and so on ...
In on extreme case the application makes 100 000 INSERTs, in the other
extreme case 100 000 UPDATES. Normally the UPDATES are more that INSERTs.
Here I notice that the first case (100 000 INSERTs) takes about 30 minues,
but the 100 000 UPDATES about 1 hour.
I can't prepare the file to use an COPY, because the application
manipulates the records from the file in a complex way.
I've also tried with vaccum to get more performance, but hat no success.
Michaela
----- Original Message -----
From: "A. Kretschmer" <andreas.kretschmer@xxxxxxxxxxxxxx>
To: <pgsql-general@xxxxxxxxxxxxxx>
Sent: Tuesday, April 11, 2006 11:48 AM
Subject: Re: [GENERAL] Performance UPDATE/INSERT
am 11.04.2006, um 11:32:55 +0200 mailte MG folgendes:
I can`t use COPY for INSERTs, because I have to manipulate each record
indiviuell.
But the problem is the UPDATEs
test=# \timing
Timing is on.
test=# update mira set y = 123;
UPDATE 150000
Time: 1874.894 ms
150 000 Records, a simple table with 2 int-columns.
Have you run vacuum? Which version? Can you tell us the explain for the
update?
Greetings
Michaela
----- Original Message ----- From: "A. Kretschmer"
Please, no silly TOFU.
HTH, Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47215, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
