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 1k6k9S-0006tr-Uf for pgsql-admin@arkaria.postgresql.org; Sat, 15 Aug 2020 00:34:27 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1k6k9Q-0000PR-U2 for pgsql-admin@arkaria.postgresql.org; Sat, 15 Aug 2020 00:34:24 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) 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 1k6k9Q-0000PK-FC for pgsql-admin@lists.postgresql.org; Sat, 15 Aug 2020 00:34:24 +0000 Received: from mail-pj1-x1041.google.com ([2607:f8b0:4864:20::1041]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from <2.andriychuk@gmail.com>) id 1k6k9O-0007tJ-4s for pgsql-admin@postgresql.org; Sat, 15 Aug 2020 00:34:23 +0000 Received: by mail-pj1-x1041.google.com with SMTP id l60so5163274pjb.3 for ; Fri, 14 Aug 2020 17:34:21 -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=BAQpU3vXzTO0JHu6AcD+89mdN0hnPDf2qUTO3ZhL7Y8=; b=Kua/NpuuGW1BV0lGkAE1z88ZAXT8U3VMaMXX/AzmQdw1DffN0y0XltQA19rG19ULfa ziH/CBJH7tc1U8TqNvL+o9zck5xIuVn7j57RGhd/KC7bVAbIz4vOwgEeyrxo99lBuCyH xp0+lzH9NtWxHOu0RXaCZmazyuUiqBk7fdcjBfbZVWCsrS0MBzSqqiNeUi18O1mQRunb dkU/3EZTCoJ5lBPvy52iQ0TjUYWZZPBtJPcHbss/3xlYn/JN7u5b/vCGMjPaqZsRc3T/ DXPVoFSGayHfeX4obv5xluDvXQku2FM2QpJ7QZHwxfX5p6u4jBkZjfrpMqw9FGm55GsI RyAw== 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=BAQpU3vXzTO0JHu6AcD+89mdN0hnPDf2qUTO3ZhL7Y8=; b=SMvoEcchjrMPnAjBr0rZkEqxEiuSpZz0nCbw27ZEpmESzQQrjgznZ9dTtKXrwgQXxC NildpNk7CLrzZlW0wYogGOATRGwvR4yCU34R9Vh8h3VVmrAp/sXFi0AQadOFJrljWnuo BHGwbh4F6m9ks4sdxPoNhrEwtb9OIE5BxoAIzF7D/5uqywJy6VMWX+Mr8Uc8foREgGbZ F3WusNWl30KJlHwIZh27T4PB2qB9XuVYXTYoVkfqi9PO9HL6Y6NcBEcjiCEWX08ulBLg OXQ3HXWwKnyDuReGSY2Out3777lnCEtzNT7P0U2o62b4PJtJlVOl3UZnLJa0vthexU4F zgKA== X-Gm-Message-State: AOAM533K4EOicV0bH5scQwEDtcaZ4vyVqtk1QQ5fSlsZI+SlxHTBP0jL LnA5M0EujGAg/CEdiylPLJg= X-Google-Smtp-Source: ABdhPJxUMSWK/zNzQ1U6DGp4iHM30K3hq+uBeItsfHrzu5DhlfZDYEzqqooUUjbwjZC/Vx37MzSwsg== X-Received: by 2002:a17:902:6f01:: with SMTP id w1mr3714721plk.49.1597451660860; Fri, 14 Aug 2020 17:34:20 -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 e26sm10420487pfj.197.2020.08.14.17.34.20 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Fri, 14 Aug 2020 17:34:20 -0700 (PDT) From: Igor Andriychuk <2.andriychuk@gmail.com> Message-Id: <7DD0EC76-5B13-479A-B1A9-7F42E14C5433@gmail.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_7A527E08-3C23-4BF5-98FB-44EDE1484BF5" 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:34:18 -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=_7A527E08-3C23-4BF5-98FB-44EDE1484BF5 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 Index would be your solution, but looks like you on v <11. You are right, I=E2=80=99ve 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.=20 I think it=E2=80=99s 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 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=E2=80=99t= worked with v<11 a lot, wouldn=E2=80=99t comment on it. > 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=_7A527E08-3C23-4BF5-98FB-44EDE1484BF5 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 Index= would be your solution, but looks like you on v <11.
You are right, I=E2=80=99ve 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=E2=80=99s 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=E2=80=99t worked with v<11 a lot, wouldn=E2=80=99= 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=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)
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=_7A527E08-3C23-4BF5-98FB-44EDE1484BF5--