pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Igor Andriychuk <2.andriychuk@gmail.com>
To: jose fuenmayor <jafn82@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Temp tables
Date: Fri, 14 Aug 2020 17:34:18 -0700
Message-ID: <7DD0EC76-5B13-479A-B1A9-7F42E14C5433@gmail.com> (raw)
In-Reply-To: <CAK74dbnrX5xp7zuUA8Lks-hJunOApvy_M554a1tpOJ_UAR_N3A@mail.gmail.com>
References: <CAK74dbmv6JStQ_7FW9-UbuY2bXoMLnW_fQ54qGCW_X8bhDngoA@mail.gmail.com>
	<5f3725c0.1c69fb81.da202.4a8d@mx.google.com>
	<CAK74db=cB6J_nO0ctEupOhQd6KQPnWcv=Y+csdoLkF7MzETNoQ@mail.gmail.com>
	<CAK74dbnrX5xp7zuUA8Lks-hJunOApvy_M554a1tpOJ_UAR_N3A@mail.gmail.com>

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 <mailto:jafn82@gmail.com>>
> Date: vie., 14 de agosto de 2020 20:07
> Subject: Re: Temp tables
> To: 2.andriychuk <2.andriychuk@gmail.com <mailto:2.andriychuk@gmail.com>>, <psql-admin@postgresql.org <mailto: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 <mailto: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 <mailto:jafn82@gmail.com>>
> Date: 8/14/20 16:27 (GMT-08:00)
> To: pgsql-admin@postgresql.org <mailto: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

view thread (6+ messages)  latest in thread

Message-ID: <7DD0EC76-5B13-479A-B1A9-7F42E14C5433@gmail.com>
Permalink:  ../7DD0EC76-5B13-479A-B1A9-7F42E14C5433@gmail.com/
Also on:    postgresql.org/message-id/7DD0EC76-5B13-479A-B1A9-7F42E14C5433@gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: 2.andriychuk@gmail.com, jafn82@gmail.com
  Subject: Re: Temp tables
  In-Reply-To: <7DD0EC76-5B13-479A-B1A9-7F42E14C5433@gmail.com>
  References: <CAK74dbmv6JStQ_7FW9-UbuY2bXoMLnW_fQ54qGCW_X8bhDngoA@mail.gmail.com> <5f3725c0.1c69fb81.da202.4a8d@mx.google.com> <CAK74db=cB6J_nO0ctEupOhQd6KQPnWcv=Y+csdoLkF7MzETNoQ@mail.gmail.com> <CAK74dbnrX5xp7zuUA8Lks-hJunOApvy_M554a1tpOJ_UAR_N3A@mail.gmail.com> <7DD0EC76-5B13-479A-B1A9-7F42E14C5433@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox