Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 2BA909FA11A for ; Mon, 29 Oct 2007 06:18:53 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 77774-07 for ; Mon, 29 Oct 2007 06:18:30 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from smtp-auth-be-04.sunrise.ch (mail-proxy-be-01.sunrise.ch [194.158.229.48]) by postgresql.org (Postfix) with ESMTP id 50E699FA0FA for ; Mon, 29 Oct 2007 06:18:32 -0300 (ADT) Received: from kbsc1 (62-167-237-145.static.adslpremium.ch [62.167.237.145]) by smtp-auth-be-04.sunrise.ch (8.13.1/8.13.5) with ESMTP id l9T9IUs6015154 for ; Mon, 29 Oct 2007 10:18:30 +0100 From: To: Subject: update from select Date: Mon, 29 Oct 2007 10:18:38 +0100 Message-ID: <0d5e01c81a0c$b0f67250$1601a8c0@kbsc1> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0D5F_01C81A15.12BD4B50" X-Mailer: Microsoft Office Outlook 11 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028 thread-index: AcgX3iauRefJueIfQ1OZhLo0pUyOvgCLnCNg X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.002 tagged_above=0 required=5 tests=BAYES_50=0.001, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200710/142 X-Sequence-Number: 29514 This is a multi-part message in MIME format. ------=_NextPart_000_0D5F_01C81A15.12BD4B50 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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 ------=_NextPart_000_0D5F_01C81A15.12BD4B50 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hello

 

I have a performance problem with an SQL = statement.

Is there a better way to do this = update:

 

UPDATE table1 SET column2 =3D = temp_table.column2, column3 =3D temp_table.column3, column4 =3D CAST(temp_table.column4 AS = date) FROM

(

 SELECT = DISTINCT

 table2.column1,=

 table2.column2,=

 table2.column3,=

 table2.column4<= /p>

 FROM table2 WHERE column4 IS NOT NULL = AND column4 <> '' AND (length(column4) =3D 10 OR length(column4) =3D = 23)

) AS temp_table

WHERE table1.column1 =3D = 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

 

------=_NextPart_000_0D5F_01C81A15.12BD4B50--