Обсуждение: 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


> 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    ===
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq
>
>

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


> 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/ ----
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match
>
>

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

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