From: | <dev(at)kbsolutions(dot)ch> |
---|---|
To: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | update from select |
Date: | 2007-10-29 09:18:38 |
Message-ID: | 0d5e01c81a0c$b0f67250$1601a8c0@kbsc1 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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 | A. Kretschmer | 2007-10-29 10:45:29 | Re: update from select |
Previous Message | Paul Lambert | 2007-10-28 23:19:55 | Re: Select into with dynamic criteria in a plpgsql function |