Fwd: Temp tables

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




---------- Forwarded message ---------
De: jose fuenmayor <jafn82@xxxxxxxxx>
Date: vie., 14 de agosto de 2020 20:07
Subject: Re: Temp tables
To: 2.andriychuk <2.andriychuk@xxxxxxxxx>, <psql-admin@xxxxxxxxxxxxxx>


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

El vie., 14 de agosto de 2020 20:01, 2.andriychuk <2.andriychuk@xxxxxxxxx> 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@xxxxxxxxx>
Date: 8/14/20 16:27 (GMT-08:00)
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

[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux