Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iWkw4-0005n6-0x for pgsql-performance@arkaria.postgresql.org; Mon, 18 Nov 2019 17:35:37 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iWkw2-0007KB-NW for pgsql-performance@arkaria.postgresql.org; Mon, 18 Nov 2019 17:35:34 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iWkw2-0007K4-92 for pgsql-performance@lists.postgresql.org; Mon, 18 Nov 2019 17:35:34 +0000 Received: from smtp38.i.mail.ru ([94.100.177.98]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iWkvy-0000xN-TR for pgsql-performance@lists.postgresql.org; Mon, 18 Nov 2019 17:35:33 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=mail.ru; s=mail2; h=Content-Type:MIME-Version:Message-ID:Date:Subject:To:From; bh=2xV+lwITAipb/zOthJrkFpl/iYg3L5RVOaIe3hJJN/o=; b=n/B4mryiOXNXJo8WQ7Yl1ybvwEJtZCD691FXRJsUHQuwaqtVfVEnKjtlLD3MkBSp9THSwZVNcZNCx0DeRfg40pbOcI0/RcZE0BIuyz4Y7D8R9Xu3m/18Mx1vKEDkcOI6gKMVYK8ZH36fyzrddT17CrA9UfE3wGjXjyCwB4V5VeQ=; Received: by smtp38.i.mail.ru with esmtpa (envelope-from ) id 1iWkvx-0006LJ-AO for pgsql-performance@lists.postgresql.org; Mon, 18 Nov 2019 20:35:29 +0300 From: "Andrew Zakharov" To: Subject: Wrong estimations and NL Anti join poor performance Date: Mon, 18 Nov 2019 20:35:29 +0300 Message-ID: <044a01d59e36$920e7020$b62b5060$@mail.ru> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_044B_01D59E4F.B75CE0A0" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AdWeLC5EUFdiDHhoRwuQ9OeiRnf29w== Content-Language: ru Authentication-Results: smtp38.i.mail.ru; auth=pass smtp.auth=andrew898@mail.ru smtp.mailfrom=andrew898@mail.ru X-77F55803: 689590B63E0A4B015A78504BD2AC294196F1F10B5E77848D4F2C9FEF8EC59809587C85C54CDE5B38D97AEE28C8E4BA0C X-7FA49CB5: FF5795518A3D127A4AD6D5ED66289B5278DA827A17800CE75FDD7770C76D593DEA1F7E6F0F101C67BD4B6F7A4D31EC0BCC500DACC3FED6E28638F802B75D45FF8AA50765F7900637DFAA318F3D833D3F8638F802B75D45FF5571747095F342E8C7A0BC55FA0FE5FC0FDBA50ACF9379835F4AD1F7E85431D0DE2906533A5B1C27389733CBF5DBD5E913377AFFFEAFD269A417C69337E82CC2CC7F00164DA146DAFE8445B8C89999725571747095F342E8C26CFBAC0749D213D2E47CDBA5A9658359CC434672EE6371117882F4460429728AD0CFFFB425014E40A5AABA2AD371193AA81AA40904B5D9A18204E546F3947CC2BF4D41862DDD6BC0837EA9F3D197644AD6D5ED66289B52E1A3F18E62937ED6A8C29749A63C2581725E5C173C3A84C380ACAAB069757940BA3038C0950A5D36B5C8C57E37DE458B5A02E28F51ECAD4422CA9DD8327EE4930A3850AC1BE2E7358189AA84889686EEC61DCFB3B85BCECE731C566533BA786A40A5AABA2AD371193C9F3DD0FB1AF5EB82E77451A5C57BD33C9F3DD0FB1AF5EB4E70A05D1297E1BBCB5012B2E24CD356 X-Mailru-Sender: C0867E0E6D7FB6C0AC1DF044B4F6B1707CD8E1860EB8D36BF23738A99CC7AD96E261594A1D9C71E476CADC040CC768ECC77752E0C033A69E4431F22F943D8E2367DBD6A6546105283453F38A29522196 X-Mras: OK List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multipart message in MIME format. ------=_NextPart_000_044B_01D59E4F.B75CE0A0 Content-Type: text/plain; charset="koi8-r" Content-Transfer-Encoding: 7bit Hello colleagues - The problem description: We're moving from 9.6 to 11.5. There is a SQL code that never ends in 11.5 but works fine in 9.6. The main cause is the optimizer considers of using NL Anti join instead of Merge in 9.6. And the root cause - wrong estimation while self-joining. System environment: CentOS Linux 3.10.0-1062.4.1.el7.x86_64 x86_64 MemTotal: 16266644 kB Intel(R) Xeon(R) CPU E7-8867 v3 @ 2.50GHz HDD - unknown PostgreSQL: PostgreSQL 11.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit shared_buffers = 1GB huge_pages = on temp_buffers = 1GB max_prepared_transactions = 128 max_connections = 500 work_mem = 256MB maintenance_work_mem = 1024MB autovacuum_work_mem = 512MB max_worker_processes = 100 max_parallel_workers_per_gather = 0 # changing this value to any others takes no effect for issue resolving max_parallel_workers = 8 checkpoint_timeout = 30min max_wal_size = 32GB min_wal_size = 8GB checkpoint_completion_target = 0.9 enable_nestloop = on # off value fixes the issue but this is wrong way random_page_cost = 4.0 effective_cache_size = 4GB default_statistics_target = 2000 Main script: -- preparation -- this table is reverted tree with tree keys position_uuid, parent_position_uuid create temporary table tmp_nsi_klp on commit drop as select k1.gid, k1.smnn_gid, k1.position_uuid, p.parent_position_uuid, k1.order_number, k1.date_start, k1.date_end, k1.is_active, coalesce(p.is_fake_series, false) as is_fake_series from nsi_klp k1 left join (select gid, unnest(parent_position_uuid) as parent_position_uuid, coalesce(array_length(parent_position_uuid, 1),0) > 1 as is_fake_series from nsi_klp where version_esklp = '2.0') p using (gid) where k1.version_esklp = '2.0' ; create unique index tmp_nsi_klp_ui on tmp_nsi_klp(gid, parent_position_uuid); analyze tmp_nsi_klp; -- working set (!!This SQL never ends in 11.5 now) create temporary table tmp_klp_replace on commit drop as select distinct on (klp_gid) * from ( select k2.gid as klp_gid, k2.smnn_gid as klp_smnn_gid, k2.position_uuid as klp_position_uuid, k2.order_number as klp_order_number, k2.is_active as klp_is_active, k1.gid as klp_child_gid, k1.smnn_gid as klp_child_smnn_gid, k1.position_uuid as klp_child_position_uuid, k1.order_number as klp_child_order_number, k1.is_active as klp_child_is_active from tmp_nsi_klp k1 join tmp_nsi_klp k2 on (k2.position_uuid = k1.parent_position_uuid) union all select k1.gid as klp_gid, k1.smnn_gid as klp_smnn_gid, k1.position_uuid as klp_position_uuid, k1.order_number as klp_order_number, k1.is_active as klp_is_active, null as klp_child_gid, null as klp_child_smnn_gid, null as klp_child_position_uuid, null as klp_child_order_number, null as klp_child_is_active from tmp_nsi_klp k1 left join tmp_nsi_klp k2 on (k1.position_uuid = k2.parent_position_uuid) left join (select position_uuid from tmp_nsi_klp where not is_fake_series group by position_uuid having count(1) > 1) klp_series on (klp_series.position_uuid = k1.position_uuid) where -- not exists(select 1 from tmp_nsi_klp k2 where k1.position_uuid = k2.parent_position_uuid) k2.gid is null -- none referenced and klp_series.position_uuid is null -- klp series with the same position_uuid ) a order by klp_gid, klp_order_number desc ; Characteristics of source table - tmp_nsi_klp: create table tmp_nsi_klp ( gid uuid NULL, -- not null by the fact smnn_gid uuid NULL, -- not null by the fact position_uuid uuid NULL, -- not null by the fact parent_position_uuid uuid NULL, order_number int8 NULL, date_start timestamp NULL, -- not null by the fact date_end timestamp NULL, is_active bool NULL, -- not null by the fact is_fake_series bool NULL -- not null by the fact ); Rows: 237279 Cols stats: https://docs.google.com/spreadsheets/d/1Ocbult13kZ64vK9nHt-_BV3EENK_ZSHFTAmR ZLISUIE/edit?usp=sharing Execution plans for problematic query - working set "create temporary table tmp_klp_replace on commit drop as": On 11.5 (option nestloop enabled): https://explain.depesz.com/s/pIzd Exec time: never finished On 9.6 (PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit): https://explain.depesz.com/s/sO0G Exec time: ~1 sec On 11.5 (option nestloop disabled): https://explain.depesz.com/s/eYzk Exec time: ~1,5 sec Construction "not exists(select 1 from tmp_nsi_klp k2 where k1.position_uuid = k2.parent_position_uuid)" works perfectly but there are lots of similar constructions in a code made for checking inclusion of data. Thus no chances to change existing code to another using not exists construction. Are there any options to bring initial statement to life and keep the server option nestloop enable? Give me a clue, pls. Thanks in advance. Andrew. ------=_NextPart_000_044B_01D59E4F.B75CE0A0 Content-Type: text/html; charset="koi8-r" Content-Transfer-Encoding: quoted-printable

Hello colleagues = –

 

The problem description:

We're moving from 9.6 to 11.5. = There is a SQL code that never ends in 11.5 but works fine in 9.6. The = main cause is the optimizer considers of using NL Anti join instead of = Merge in 9.6. And the root cause - wrong estimation while = self-joining.

 

System environment:

CentOS Linux = 3.10.0-1062.4.1.el7.x86_64 x86_64

MemTotal:=9A=9A=9A=9A=9A=9A = 16266644 kB

Intel(R) Xeon(R) CPU E7-8867 v3 @ = 2.50GHz

HDD = - unknown

 

PostgreSQL:

PostgreSQL 11.5 on = x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat = 4.8.5-36), 64-bit

shared_buffers =3D 1GB

huge_pages =3D = on

temp_buffers =3D 1GB

max_prepared_transactions =3D = 128

max_connections =3D 500

work_mem =3D = 256MB

maintenance_work_mem =3D 1024MB

autovacuum_work_mem =3D = 512MB

max_worker_processes =3D 100

max_parallel_workers_per_gather =3D = 0 # changing this value to any others takes no effect for issue = resolving

max_parallel_workers =3D 8

checkpoint_timeout =3D = 30min

max_wal_size =3D 32GB

min_wal_size =3D = 8GB

checkpoint_completion_target =3D = 0.9

enable_nestloop =3D = on=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A # off value fixes the = issue but this is wrong way

random_page_cost =3D = 4.0

effective_cache_size =3D 4GB

default_statistics_target =3D = 2000

 

Main script:

 

=9A -- preparation

=9A -- this table is = reverted tree with tree keys position_uuid, = parent_position_uuid

=9A create temporary = table tmp_nsi_klp on commit drop as

=9A select

=9A=9A=9A = k1.gid,

=9A=9A=9A = k1.smnn_gid,

=9A=9A=9A = k1.position_uuid,

=9A=9A=9A = p.parent_position_uuid,

=9A=9A=9A = k1.order_number,

=9A=9A=9A = k1.date_start,

=9A=9A=9A = k1.date_end,

=9A=9A=9A = k1.is_active,

=9A=9A=9A = coalesce(p.is_fake_series, false) as = is_fake_series

=9A = from

=9A=9A=9A nsi_klp = k1

=9A=9A=9A=9A=9A left join (select = gid, unnest(parent_position_uuid) as parent_position_uuid, = coalesce(array_length(parent_position_uuid, 1),0) > 1 as = is_fake_series from nsi_klp where version_esklp =3D '2.0') p using = (gid)

=9A where

=9A=9A=9A k1.version_esklp =3D '2.0'

=9A ;

 

=9A create unique index tmp_nsi_klp_ui on tmp_nsi_klp(gid, = parent_position_uuid);

 

=9A analyze = tmp_nsi_klp;

 

=9A -- working set = (!!This SQL never ends in 11.5 now)

=9A create temporary table tmp_klp_replace on commit drop = as

=9A=9A=9A select distinct on = (klp_gid)

=9A=9A=9A=9A=9A = *

=9A=9A=9A from = (

=9A=9A=9A=9A=9A = select

=9A=9A=9A=9A=9A=9A=9A = k2.gid=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A as = klp_gid,

=9A=9A=9A=9A=9A=9A=9A = k2.smnn_gid=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A as = klp_smnn_gid,

=9A=9A=9A=9A=9A=9A=9A = k2.position_uuid=9A=9A=9A=9A=9A=9A=9A as = klp_position_uuid,

=9A=9A=9A=9A=9A=9A=9A = k2.order_number=9A=9A=9A=9A=9A=9A=9A=9A as = klp_order_number,

=9A=9A=9A=9A=9A=9A=9A = k2.is_active=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A as = klp_is_active,

=9A=9A=9A=9A=9A=9A=9A = k1.gid=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A as = klp_child_gid,

=9A=9A=9A=9A=9A=9A=9A = k1.smnn_gid=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A as = klp_child_smnn_gid,

=9A=9A=9A=9A=9A=9A=9A = k1.position_uuid=9A=9A=9A=9A=9A=9A=9A as = klp_child_position_uuid,

=9A=9A=9A=9A=9A=9A=9A = k1.order_number=9A=9A=9A=9A=9A=9A=9A=9A as = klp_child_order_number,

=9A=9A=9A=9A=9A=9A=9A = k1.is_active=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A as = klp_child_is_active

=9A=9A=9A=9A=9A = from

=9A=9A=9A=9A=9A=9A=9A tmp_nsi_klp = k1

=9A=9A=9A=9A=9A=9A=9A=9A=9A join = tmp_nsi_klp k2 on (k2.position_uuid =3D = k1.parent_position_uuid)

=9A=9A=9A=9A=9A union = all

=9A=9A=9A=9A=9A = select

=9A=9A=9A=9A=9A=9A=9A = k1.gid=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A as = klp_gid,

=9A=9A=9A=9A=9A=9A=9A = k1.smnn_gid=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A as = klp_smnn_gid,

=9A=9A=9A=9A=9A=9A=9A = k1.position_uuid=9A=9A=9A=9A=9A=9A=9A as = klp_position_uuid,

=9A=9A=9A=9A=9A=9A=9A = k1.order_number=9A=9A=9A=9A=9A=9A=9A=9A as = klp_order_number,

=9A=9A=9A=9A=9A=9A=9A = k1.is_active=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A as = klp_is_active,

=9A=9A=9A=9A=9A=9A=9A = null=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A as = klp_child_gid,

=9A=9A=9A=9A=9A=9A=9A = null=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A as = klp_child_smnn_gid,

=9A=9A=9A=9A=9A=9A=9A = null=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A as = klp_child_position_uuid,

=9A=9A=9A=9A=9A=9A=9A = null=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A as = klp_child_order_number,

=9A=9A=9A=9A=9A=9A=9A = null=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A as = klp_child_is_active

=9A=9A=9A=9A=9A = from

=9A=9A=9A=9A=9A=9A=9A tmp_nsi_klp = k1

=9A=9A=9A=9A=9A=9A=9A=9A=9A left = join tmp_nsi_klp k2 on (k1.position_uuid =3D = k2.parent_position_uuid)

=9A=9A=9A=9A=9A=9A=9A=9A=9A left join (select position_uuid from = tmp_nsi_klp where not is_fake_series group by position_uuid having = count(1) > 1) klp_series on (klp_series.position_uuid =3D = k1.position_uuid)

=9A=9A=9A=9A=9A = where

=9A=9A=9A=9A=9A=9A=9A -- not = exists(select 1 from tmp_nsi_klp k2 where k1.position_uuid =3D = k2.parent_position_uuid)

=9A=9A=9A=9A=9A=9A=9A = k2.gid is = null=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A= -- none referenced

=9A=9A=9A=9A=9A=9A=9A = and klp_series.position_uuid is null=9A -- klp series with the same = position_uuid

=9A=9A=9A ) = a

=9A=9A=9A order = by

=9A=9A=9A=9A=9A = klp_gid,

=9A=9A=9A=9A=9A klp_order_number = desc

=9A ;

 

Characteristics of source table = - tmp_nsi_klp:

 

create table = tmp_nsi_klp (

=9A=9A=9A=9A gid uuid = NULL,=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A -- not null by = the fact

=9A=9A=9A=9A smnn_gid uuid = NULL,=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A -- not null by the = fact

=9A=9A=9A=9A position_uuid uuid = NULL,=9A=9A=9A=9A=9A=9A=9A -- not null by the = fact

=9A=9A=9A=9A parent_position_uuid = uuid NULL,

=9A=9A=9A=9A order_number int8 = NULL,

=9A=9A=9A=9A date_start timestamp = NULL,=9A=9A=9A=9A=9A -- not null by the fact

=9A=9A=9A=9A date_end timestamp NULL,

=9A=9A=9A=9A is_active bool NULL,=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A = -- not null by the fact

=9A=9A=9A=9A = is_fake_series bool NULL=9A=9A=9A=9A=9A=9A=9A -- not null by the = fact

);

 

Rows: = 237279

 

Cols = stats:

https://docs.google.com/spreadshee= ts/d/1Ocbult13kZ64vK9nHt-_BV3EENK_ZSHFTAmRZLISUIE/edit?usp=3Dsharing<= o:p>

 

Execution plans for problematic query - working set = "create temporary table tmp_klp_replace on commit drop = as":

 

On 11.5 (option nestloop = enabled):

https://explain.depesz.com/s/p= Izd

Exec time: never finished

 

On 9.6 (PostgreSQL 9.6.2 on = x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat = 4.8.5-11), 64-bit):

https://explain.depesz.com/s/s= O0G

Exec time: ~1 sec

 

On 11.5 (option nestloop = disabled):

https://explain.depesz.com/s/e= Yzk

Exec time: ~1,5 sec

 

Construction "not = exists(select 1 from tmp_nsi_klp k2 where k1.position_uuid =3D = k2.parent_position_uuid)" works perfectly but there are lots of = similar constructions in a code made for checking inclusion of data. = Thus no chances to change existing code to another using not exists = construction. Are there any options to bring initial statement to life = and keep the server option nestloop enable?

Give me a clue, = pls.

Thanks = in advance.

Andrew.

 

------=_NextPart_000_044B_01D59E4F.B75CE0A0--