|
Hi Jose, If table2 is really big and you run this update on regular base just make sure you have a covering index on pair x and field2 with field1 in include option, then put both x=z and a.field2=b.field2 to where clause. You don't have to use subquery, just table straight forward, index will do the trick for you. Create index <index name> on table2(x, field2) include(field1); But include is available starting from v11. If version of your PostgreSQL < 11, use with to express your subquery, then use it in join. Best, Igor Sent from my Verizon, Samsung Galaxy smartphone -------- Original message -------- From: jose fuenmayor <jafn82@xxxxxxxxx> Date: 8/14/20 16:27 (GMT-08:00) To: pgsql-admin@xxxxxxxxxxxxxx Subject: Temp tables Hi all , i have a doubt wich query performs better
Update table a set field1=b.field 1 From (select field1,field2 From table2 where x=z ) b Where a.field2=b.field2 Or Create temp table y as select field1,field2 From table2 where x=z; Update table a set field1=b.field1 From y as b Where a.field2=b.field2 I ve been told not to use subquerys, but i think this refers to Select * from table where field1 in (select field1 from table2) Any thoughts? Thanks people y'all |
