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:24:34 -0700
Message-ID: <E3DDF523-2217-462B-8036-807E9F1F77CA@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>
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 <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
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 using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: 2.andriychuk@gmail.com, jafn82@gmail.com
Subject: Re: Temp tables
In-Reply-To: <E3DDF523-2217-462B-8036-807E9F1F77CA@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