Fwd: Temp tables

---------- Forwarded message ---------
De: jose fuenmayor <jafn82(at)gmail(dot)com>
Date: vie., 14 de agosto de 2020 20:07
Subject: Re: Temp tables
To: 2.andriychuk <2(dot)andriychuk(at)gmail(dot)com>, <psql-admin(at)postgresql(dot)org>

Could you show me the modified query using with and join??

El vie., 14 de agosto de 2020 20:01, 2.andriychuk <2(dot)andriychuk(at)gmail(dot)com>
escribió:

> 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(at)gmail(dot)com>
> Date: 8/14/20 16:27 (GMT-08:00)
> To: pgsql-admin(at)postgresql(dot)org
> 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
>