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 > > ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
