Re: Temp tables - Mailing list pgsql-admin

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,

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