Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1k6k06-0006XG-KC for pgsql-admin@arkaria.postgresql.org; Sat, 15 Aug 2020 00:24:46 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1k6k05-0003Bh-CZ for pgsql-admin@arkaria.postgresql.org; Sat, 15 Aug 2020 00:24:45 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from <2.andriychuk@gmail.com>) id 1k6k05-0003BZ-18 for pgsql-admin@lists.postgresql.org; Sat, 15 Aug 2020 00:24:45 +0000 Received: from mail-pj1-x1036.google.com ([2607:f8b0:4864:20::1036]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from <2.andriychuk@gmail.com>) id 1k6jzy-0008NK-Dq for pgsql-admin@postgresql.org; Sat, 15 Aug 2020 00:24:44 +0000 Received: by mail-pj1-x1036.google.com with SMTP id i92so6224649pje.0 for ; Fri, 14 Aug 2020 17:24:37 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=from:message-id:mime-version:subject:date:in-reply-to:cc:to :references; bh=9pXJdd1nPjOkZ/Nh8snzDA56QVx7SlrxL6NbN653jpg=; b=kgP/+vbC68f5G1U0mrIhVm5uUCb6h9C9Z0v4MpcYfAqSfodL0kPxT4gREP+eEPE+P1 /IF7rKwgmPbiW6DMm6vRFu9H/+Wi/9IV+WQI9t45Z71u9bhqBJ1u1BQ1d2JlubKfuk3X JwK1YcULorIIxn6zjhnSwCL1aOXbIoPPR2fZpY4A54tZhL6guNSSOpt94f24O77SMqtI TkBGa8iLDKrq+idg8RjAcdgFtb9MIQK1R7BCk5Vv+bZb/V6plOYLbZZR6ra1VHozw6g4 KSG7QtBkyetlc8VEbvqxpZYfVc76SFhiIFNIE1F4N29CfIdw/cHmZK7bA7xdEpUyKj5/ oMng== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:message-id:mime-version:subject:date :in-reply-to:cc:to:references; bh=9pXJdd1nPjOkZ/Nh8snzDA56QVx7SlrxL6NbN653jpg=; b=RLe0CFCAj6+LJFhzNPsahjF/bKxZZYTKfYBJkjPQZNdfw4F4aCYBhunxGceTZUFQy+ GIYUt3rMHmllJKJqm8aXfGf7rkcy5+QGnD//5Z092qWnkJ8BnrXMaA4uN+YJsBbrB8iC GzB+ULB6jDhjMtE/2gWMNHIP93zFzovzmadx9Q7Z6PaFvUdALfKk1X8BtfiCvrKpVPxG NZOdC6WOa0qgn4tn4sWjmFicdZoZXOAf1TX3tS9lVhgOPO/60hnGGFs2mikCEAIpS9w7 UznuaT/wnp9R9lqVlu8Znv5SFDAVR3yQWLgRaJtd9iRSoDPV3kBPIRmyhYToJNTT4qXs nbcg== X-Gm-Message-State: AOAM533Q+M9lbFnwKPJJdn7s+0RRONgq0TkLryCfDM3nE/zx6qpLzngR cnIOQw97cBJp9pOziKDTAOs= X-Google-Smtp-Source: ABdhPJwAswXWXxdhTBDCR5DiWq2X6ORCBkGRjLgj72p7ubGBoK/3zz6wlyJJC0MNDU/6tYDwAwGQKw== X-Received: by 2002:a17:902:123:: with SMTP id 32mr3519261plb.143.1597451076140; Fri, 14 Aug 2020 17:24:36 -0700 (PDT) Received: from [192.168.1.19] (198-27-163-68.fiber.dynamic.sonic.net. [198.27.163.68]) by smtp.gmail.com with ESMTPSA id u40sm9881273pjb.39.2020.08.14.17.24.35 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Fri, 14 Aug 2020 17:24:35 -0700 (PDT) From: Igor Andriychuk <2.andriychuk@gmail.com> Message-Id: Content-Type: multipart/alternative; boundary="Apple-Mail=_D5DCDE33-CDFB-4E48-9991-A6DDF84C4388" Mime-Version: 1.0 (Mac OS X Mail 13.4 \(3608.120.23.2.1\)) Subject: Re: Temp tables Date: Fri, 14 Aug 2020 17:24:34 -0700 In-Reply-To: Cc: pgsql-admin@postgresql.org To: jose fuenmayor References: <5f3725c0.1c69fb81.da202.4a8d@mx.google.com> X-Mailer: Apple Mail (2.3608.120.23.2.1) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail=_D5DCDE33-CDFB-4E48-9991-A6DDF84C4388 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 I said stupid thing :-) you can=E2=80=99t use with with update only with = select, I forgot you are about update. > On Aug 14, 2020, at 5:09 PM, jose fuenmayor wrote: >=20 >=20 > ---------- Forwarded message --------- > De: jose fuenmayor > > Date: vie., 14 de agosto de 2020 20:07 > Subject: Re: Temp tables > To: 2.andriychuk <2.andriychuk@gmail.com = >, > >=20 >=20 > Could you show me the modified query using with and join?? >=20 > El vie., 14 de agosto de 2020 20:01, 2.andriychuk = <2.andriychuk@gmail.com > escribi=C3=B3: > Hi Jose, >=20 > 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=3Dz and a.field2=3Db.field2 to where = clause.=20 > You don't have to use subquery, just table straight forward, index = will do the trick for you. >=20 > Create index on table2(x, field2) include(field1); >=20 > But include is available starting from v11.=20 >=20 > If version of your PostgreSQL < 11, use with to express your subquery, = then use it in join. >=20 > Best, > Igor >=20 >=20 > Sent from my Verizon, Samsung Galaxy smartphone >=20 >=20 > -------- Original message -------- > From: jose fuenmayor > > Date: 8/14/20 16:27 (GMT-08:00) > To: pgsql-admin@postgresql.org > Subject: Temp tables >=20 > Hi all , i have a doubt wich query performs better > Update table a set field1=3Db.field 1 > =46rom (select field1,field2 >=20 > =46rom table2 where x=3Dz > ) b > Where a.field2=3Db.field2 >=20 > Or >=20 > Create temp table y as > select field1,field2 > =46rom table2 where x=3Dz; >=20 > Update table a=20 > set field1=3Db.field1 > =46rom y as b > Where a.field2=3Db.field2 >=20 > I ve been told not to use subquerys, but i think this refers to >=20 > Select * from table where field1 in (select field1 from table2) >=20 > Any thoughts? > Thanks people y'all --Apple-Mail=_D5DCDE33-CDFB-4E48-9991-A6DDF84C4388 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 I = said stupid thing :-) you can=E2=80=99t 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=C3=B3:
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=3Dz and a.field2=3Db.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=3Db.field 1
=46rom (select field1,field2

=46rom table2 where x=3Dz
) b
Where a.field2=3Db.field2

Or

Create temp table y as
select field1,field2
=46rom table2 where x=3Dz;

Update = table a 
set field1=3Db.field1
=46rom y as b
Where a.field2=3Db.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

= --Apple-Mail=_D5DCDE33-CDFB-4E48-9991-A6DDF84C4388--