From: Pavel Stehule <pavel.stehule@gmail.com>
To: dev@kbsolutions.ch <dev@kbsolutions.ch>
Cc: pgsql-sql@postgresql.org
Subject: Re: update from select
Date: Mon, 29 Oct 2007 12:03:27 +0100
Message-ID: <162867790710290403j21699b87jb91ead9c503acfb3@mail.gmail.com> (raw)
In-Reply-To: <0d5e01c81a0c$b0f67250$1601a8c0@kbsc1>
References: <0d5e01c81a0c$b0f67250$1601a8c0@kbsc1>
Hello
you use corelated subquery and that is slow for thausands rows. Use
PostgreSQL's extension
UPDATE table1 SET column2 = t,colum2, ....
FROM table2 t
WHERE table1.column1 = t.column1 and t.column4 is not null and ...
http://www.postgresql.org/docs/8.2/interactive/sql-update.html
Regards
Pavel Stehule
> Hello
>
>
>
> I have a performance problem with an SQL statement.
>
> Is there a better way to do this update:
>
>
>
> UPDATE table1 SET column2 = temp_table.column2, column3 =
> temp_table.column3, column4 = CAST(temp_table.column4 AS date) FROM
>
> (
>
> SELECT DISTINCT
>
> table2.column1,
>
> table2.column2,
>
> table2.column3,
>
> table2.column4
>
> FROM table2 WHERE column4 IS NOT NULL AND column4 <> '' AND
> (length(column4) = 10 OR length(column4) = 23)
>
> ) AS temp_table
>
> WHERE table1.column1 = temp_table.column1;
>
>
>
> The select by it's own takes around 1 second. The Update is around 120'000
> rows. I got an index on column1. The whole query needs around 16 minutes.
>
> The same procedure on MSSQL needs around 30 seconds. I hope to get it too in
> Postgres…
>
>
>
> Please help me.
>
>
>
> Regards
>
>
>
> Reto
>
>
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: pavel.stehule@gmail.com, dev@kbsolutions.ch
Subject: Re: update from select
In-Reply-To: <162867790710290403j21699b87jb91ead9c503acfb3@mail.gmail.com>
* 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