<dev@kbsolutions.ch> writes: > 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; This looks seriously fishy. Is table2.column1 unique? If it is then you don't need the DISTINCT. If it isn't, you are in great danger of trying to update (some) table1 rows multiple times; which is bad, both because it wastes cycles and because you have no idea which of the matching table2 rows will "win" the update. I think you first need to think clearly about what you're doing ... regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings

<dev@kbsolutions.ch> writes: > Hash Join (cost=10827.45..25950.05 rows=4906 width=1191) (actual > time=586.251..2852.691 rows=111306 loops=1) > ... > Total runtime: 633548.404 ms So you're worried about the wrong thing entirely. The query is taking less than 3 seconds, which may be reasonable considering it's producing 111000 join rows. The big problem is the other 630 seconds, which is evidently update overhead. I'm wondering if you have any triggers or foreign keys leading to or from this table. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing list cleanly