Re: Temp tables

Thanks folks

El vie., 14 de agosto de 2020 20:34, Igor Andriychuk <2(dot)andriychuk(at)gmail(dot)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
> 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(at)gmail(dot)com> wrote:
>
>
> ---------- 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
>>
>
>