From: Franco Bruno Borghesi <franco@akyasociados.com.ar>
To: mrblonde@locked.myftp.org
Cc: pgsql-sql@postgresql.org
Subject: Re: update from multiple rows
Date: Tue, 25 Jan 2005 21:12:18 -0300
Message-ID: <41F6E062.2080009@akyasociados.com.ar> (raw)
In-Reply-To: <20050124233220.6CA161C00085@mwinf1206.wanadoo.fr>
References: <20050124233220.6CA161C00085@mwinf1206.wanadoo.fr>
updating 40.000 records should take no longer than a couple of minutes.
I think you should optimise your query before going any further.
You have an inner SELECT sentence that executes before anything. It
joins EVERY row in your table (1,000,000+) with at most 3 other rows in
the same table, so you will end up with about 3,000,000+ rows... but you
are interested in only 40,000 rows!
To make it simple, add a WHERE condition to fetch only the 40.000 rows
you are interested in and discard the others. Make sure also you have
indexed the attributes you are filtering on, and the date attribute too.
You should use EXPLAIN ANALYZE on the inner query to check how it improves.
Once your SELECT query runs fast enough, the UPDATE should go much
faster too.
The number of columns matters, but as I said, I don't think it's an
UPDATE problem.
If you don't find the way to speed your query up, try posting to the
performance list.
mrblonde@locked.myftp.org wrote:
>Thanks a lot.. That is what i searched.. In fact your query is very good for little changes, but i will have to use another method when updating all my rows because the performance is not very good alas.
>
>My data set contains something like 40000 rows to update in 1+ million records and data_raw, data_sys are of type "real"... The complete update took 40 minutes on a 256Mo, athlon 2400, kernel 2.6 and with no charge during the execution of the query.
>
>Is this normal ? The number of columns of the table does it matter a lot (the table contains 12 reals and 4 integers) ?
>
>I found that using an intermediate table which stock for every row the value before and the value after helps to gain speed... But it is not a very nice way i think..
>
>Thanks again :)
>Etienne
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: franco@akyasociados.com.ar, mrblonde@locked.myftp.org
Subject: Re: update from multiple rows
In-Reply-To: <41F6E062.2080009@akyasociados.com.ar>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox