Thread: 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
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@gmail.com>
Date: 8/14/20 16:27 (GMT-08:00)
To: pgsql-admin@postgresql.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
---------- Forwarded message ---------
De: jose fuenmayor <jafn82@gmail.com>
Date: vie., 14 de agosto de 2020 20:07
Subject: Re: Temp tables
To: 2.andriychuk <2.andriychuk@gmail.com>, <psql-admin@postgresql.org>
De: jose fuenmayor <jafn82@gmail.com>
Date: vie., 14 de agosto de 2020 20:07
Subject: Re: Temp tables
To: 2.andriychuk <2.andriychuk@gmail.com>, <psql-admin@postgresql.org>
Could you show me the modified query using with and join??
El vie., 14 de agosto de 2020 20:01, 2.andriychuk <2.andriychuk@gmail.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,IgorSent from my Verizon, Samsung Galaxy smartphone-------- Original message --------From: jose fuenmayor <jafn82@gmail.com>Date: 8/14/20 16:27 (GMT-08:00)Subject: Temp tablesHi all , i have a doubt wich query performs betterUpdate table a set field1=b.field 1From (select field1,field2From table2 where x=z) bWhere a.field2=b.field2OrCreate temp table y asselect field1,field2From table2 where x=z;Update table aset field1=b.field1From y as bWhere a.field2=b.field2I ve been told not to use subquerys, but i think this refers toSelect * from table where field1 in (select field1 from table2)Any thoughts?Thanks people y'all
I said stupid thing :-) you can’t use with with update only with select, I forgot you are about update.
On Aug 14, 2020, at 5:09 PM, jose fuenmayor <jafn82@gmail.com> wrote:---------- Forwarded message ---------
De: jose fuenmayor <jafn82@gmail.com>
Date: vie., 14 de agosto de 2020 20:07
Subject: Re: Temp tables
To: 2.andriychuk <2.andriychuk@gmail.com>, <psql-admin@postgresql.org>Could you show me the modified query using with and join??El vie., 14 de agosto de 2020 20:01, 2.andriychuk <2.andriychuk@gmail.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,IgorSent from my Verizon, Samsung Galaxy smartphone-------- Original message --------From: jose fuenmayor <jafn82@gmail.com>Date: 8/14/20 16:27 (GMT-08:00)Subject: Temp tablesHi all , i have a doubt wich query performs betterUpdate table a set field1=b.field 1From (select field1,field2From table2 where x=z) bWhere a.field2=b.field2OrCreate temp table y asselect field1,field2From table2 where x=z;Update table aset field1=b.field1From y as bWhere a.field2=b.field2I ve been told not to use subquerys, but i think this refers toSelect * from table where field1 in (select field1 from table2)Any thoughts?Thanks people y'all
Index would be your solution, but looks like you on v <11.
You are right, I’ve seen a dramatic performance change in SQL Server when I used temporary table and then dynamically build an index on required columns and use it in the following queries.
I think it’s because in some cases SQL Server does a poor job on query optimization.
In case if you are using v <11 you can try to build index with three columns
Create index <index name> on table2(x, field2, field1);
Make sure the column which you are not using in where clause conditions comes last.
Then play with execution plan to check if it uses your index or not.
Also try to not use a subquery but table straight forward. Haven’t worked with v<11 a lot, wouldn’t comment on it.
On Aug 14, 2020, at 5:09 PM, jose fuenmayor <jafn82@gmail.com> wrote:---------- Forwarded message ---------
De: jose fuenmayor <jafn82@gmail.com>
Date: vie., 14 de agosto de 2020 20:07
Subject: Re: Temp tables
To: 2.andriychuk <2.andriychuk@gmail.com>, <psql-admin@postgresql.org>Could you show me the modified query using with and join??El vie., 14 de agosto de 2020 20:01, 2.andriychuk <2.andriychuk@gmail.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,IgorSent from my Verizon, Samsung Galaxy smartphone-------- Original message --------From: jose fuenmayor <jafn82@gmail.com>Date: 8/14/20 16:27 (GMT-08:00)Subject: Temp tablesHi all , i have a doubt wich query performs betterUpdate table a set field1=b.field 1From (select field1,field2From table2 where x=z) bWhere a.field2=b.field2OrCreate temp table y asselect field1,field2From table2 where x=z;Update table aset field1=b.field1From y as bWhere a.field2=b.field2I ve been told not to use subquerys, but i think this refers toSelect * from table where field1 in (select field1 from table2)Any thoughts?Thanks people y'all
Thanks folks
El vie., 14 de agosto de 2020 20:34, Igor Andriychuk <2.andriychuk@gmail.com> escribió:
Index would be your solution, but looks like you on v <11.You are right, I’ve seen a dramatic performance change in SQL Server when I used temporary table and then dynamically build an index on required columns and use it in the following queries.I think it’s because in some cases SQL Server does a poor job on query optimization.In case if you are using v <11 you can try to build index with three columnsCreate index <index name> on table2(x, field2, field1);Make sure the column which you are not using in where clause conditions comes last.Then play with execution plan to check if it uses your index or not.Also try to not use a subquery but table straight forward. Haven’t worked with v<11 a lot, wouldn’t comment on it.On Aug 14, 2020, at 5:09 PM, jose fuenmayor <jafn82@gmail.com> wrote:---------- Forwarded message ---------
De: jose fuenmayor <jafn82@gmail.com>
Date: vie., 14 de agosto de 2020 20:07
Subject: Re: Temp tables
To: 2.andriychuk <2.andriychuk@gmail.com>, <psql-admin@postgresql.org>Could you show me the modified query using with and join??El vie., 14 de agosto de 2020 20:01, 2.andriychuk <2.andriychuk@gmail.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,IgorSent from my Verizon, Samsung Galaxy smartphone-------- Original message --------From: jose fuenmayor <jafn82@gmail.com>Date: 8/14/20 16:27 (GMT-08:00)Subject: Temp tablesHi all , i have a doubt wich query performs betterUpdate table a set field1=b.field 1From (select field1,field2From table2 where x=z) bWhere a.field2=b.field2OrCreate temp table y asselect field1,field2From table2 where x=z;Update table aset field1=b.field1From y as bWhere a.field2=b.field2I ve been told not to use subquerys, but i think this refers toSelect * from table where field1 in (select field1 from table2)Any thoughts?Thanks people y'all