Skip site navigation (1) Skip section navigation (2)

Re: update 600000 rows

From: "Merlin Moncure" <mmoncure(at)gmail(dot)com>
To: andrew(at)pillette(dot)com
Cc: okparanoid(at)free(dot)fr, pgsql-performance(at)postgresql(dot)org
Subject: Re: update 600000 rows
Date: 2007-12-16 18:52:00
Message-ID: b42b73150712161052h5bfade28h1a5043c457a2a6a0@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Dec 16, 2007 12:21 AM,  <andrew(at)pillette(dot)com> wrote:
> Loïc Marteau <okparanoid(at)free(dot)fr> wrote ..
> > Steve Crawford wrote:
> > > If this
> > > is correct, I'd first investigate simply loading the csv data into a
> > > temporary table, creating appropriate indexes, and running a single
> > > query to update your other table.
>
> My experience is that this is MUCH faster. My predecessor in my current position was doing an update from a csv file line by line with perl. That is one reason he is my predecessor. Performance did not justify continuing his contract.
>
> > i can try this. The problem is that i have to make an insert if the
> > update don't have affect a rows (the rows don't exist yet). The number
> > of rows affected by insert is minor regards to the numbers of updated
> > rows and was 0 when i test my script). I can do with a temporary table
> > : update all the possible rows and then insert the rows that are in
> > temporary table and not in the production table with a 'not in'
> > statement. is this a correct way ?
>
> That's what I did at first, but later I found better performance with a TRIGGER on the permanent table that deletes the target of an UPDATE, if any, before the UPDATE. That's what PG does anyway, and now I can do the entire UPDATE in one command.

that's very clever, and probably is the fastest/best way to do it.
you can even temporarily add the trigger a transaction...I am going to
try this out in a couple of things (I currently do these type of
things in two statements) and see how it turns out.

merlin

In response to

pgsql-performance by date

Next:From: Craig JamesDate: 2007-12-16 19:03:11
Subject: Re: libgcc double-free, backend won't die
Previous:From: Joshua D. DrakeDate: 2007-12-16 18:37:28
Subject: Re: SELECT * FROM table is too slow

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group