From: | "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | "dev(at)kbsolutions(dot)ch" <dev(at)kbsolutions(dot)ch> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: update from select |
Date: | 2007-10-29 11:03:27 |
Message-ID: | 162867790710290403j21699b87jb91ead9c503acfb3@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2007-10-29 14:30:08 | Re: update from select |
Previous Message | A. Kretschmer | 2007-10-29 10:45:29 | Re: update from select |