Performance UPDATE/INSERT

am 11.04.2006, um 11:08:57 +0200 mailte MG folgendes:

Hello,

I have about 100 000 records, which need about 30 minutes to write them with single INSERTs into PostgreSQL.

You should better use the COPY - command for bulk inserts. This is
faster.

HTH, Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47215, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===

Hi,

On Tuesday 11 April 2006 11:08, MG wrote:
| I have about 100 000 records, which need about 30 minutes to write them
| with single INSERTs into PostgreSQL. If I go through these 100 000 records
| and make an UPDATE on each record, it takes 1 hour.
|
| Can anyone tell me, about his experience of the performance of INSERT and
| UPDATE.

how about using COPY for the import? Or at least putting the INSERTs into
a transaction using BEGIN and COMMIT? I would do the same with the UPDATEs:
put it into a transaction frame.

Ciao,
Thomas

--
Thomas Pundt <thomas.pundt@rp-online.de> ---- http://rp-online.de/ ----

I can`t use COPY for INSERTs, because I have to manipulate each record
indiviuell.

But the problem is the UPDATEs

Greetings
Michaela

----- Original Message -----
From: "A. Kretschmer" <andreas.kretschmer@schollglas.com>
To: <pgsql-general@postgresql.org>
Sent: Tuesday, April 11, 2006 11:20 AM
Subject: Re: [GENERAL] Performance UPDATE/INSERT

I can`t use COPY or put it in a transaction, because the application which
writes in PostgreSQL decides if it`s an INSERT or a UPDATE which depends on
different conditions and also manipulate the records.
In on extreme case it can be only INSERTs, in the other extreme case all can
be UPDATES. Normally the UPDATES are more that INSERTs.

I have to improve the performance of the UPDATES and I was wondering why the
UPDATEs need twice as long as the INSERTs.

Greetings
Michaela

----- Original Message -----
From: "Thomas Pundt" <mlists@rp-online.de>
To: <pgsql-general@postgresql.org>
Sent: Tuesday, April 11, 2006 11:30 AM
Subject: Re: [GENERAL] Performance UPDATE/INSERT

a single complex insert/update sql would be always faster compared to having
a cursor and manipulating the data.
Again, it depends on what is the logic, and where actually you have the
data.

cool.
L.

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 ===

Hi,

On Tuesday 11 April 2006 11:40, MG wrote:
| I  can`t use COPY or put it in a transaction, because the application which
| writes in PostgreSQL decides if it`s an INSERT or a UPDATE which depends on
| different conditions and also manipulate the records.

I don't see a reason that stops you from using a transaction here...

Ciao,
Thomas

--
Thomas Pundt <thomas.pundt@rp-online.de> ---- http://rp-online.de/ ----

MG wrote:

I have to improve the performance of the UPDATES and I was wondering why
the UPDATEs need twice as long as the INSERTs.

IIRC, an UPDATE is a DELETE/INSERT combo. That's because the original
record still needs to be visible to other existing transactions (because
of MVCC). You can't change its data but need to create a new record
instead and mark the original as obsolete.

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

// Integrate Your World //

am 11.04.2006, um 12:15:41 +0200 mailte Alban Hertroys folgendes:

MG wrote:

I have to improve the performance of the UPDATES and I was wondering why
the UPDATEs need twice as long as the INSERTs.

IIRC, an UPDATE is a DELETE/INSERT combo. That's because the original
record still needs to be visible to other existing transactions (because of
MVCC). You can't change its data but need to create a new record instead
and mark the original as obsolete.

Correctly, and thats why we need VACUUM to clean the space on the disk.

HTH, Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47215, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===

Turn off your indices on the table... do the inserts... do the updates... rebuild the indices....

""MG"" <pgsql-general@carladata.de> wrote in message news:006701c65d47$90af8140$340aa8c0@geisslinger...
Hello,

I have about 100 000 records, which need about 30 minutes to write them with single INSERTs into PostgreSQL.
If I go through these 100 000 records and make an UPDATE on each record, it takes 1 hour.

Can anyone tell me, about his experience of the performance of INSERT and UPDATE.

Greetings
Michaela