Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tFg6i-00Ey7D-F6 for pgsql-performance@arkaria.postgresql.org; Mon, 25 Nov 2024 20:54:58 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tFg6g-000RMY-Ov for pgsql-performance@arkaria.postgresql.org; Mon, 25 Nov 2024 20:54:54 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tFg6f-000RMD-CU for pgsql-performance@lists.postgresql.org; Mon, 25 Nov 2024 20:54:54 +0000 Received: from mail-sg2apc01on20712.outbound.protection.outlook.com ([2a01:111:f403:200f::712] helo=APC01-SG2-obe.outbound.protection.outlook.com) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tFg6Z-003kc0-Jt for pgsql-performance@lists.postgresql.org; Mon, 25 Nov 2024 20:54:51 +0000 ARC-Seal: i=1; a=rsa-sha256; s=arcselector10001; d=microsoft.com; cv=none; b=T+sKsdHupmuz4trYUvUzo/bpktBc40fCQwPEHH7rihYRMurHEKmKfZ1ONqo0TZc6JY7LkmIZ6yI1VlVV9lTuYXUGWmRzzwcwvT/gZzdGxLS9qyW7XTBb05a53KkUuglm65rtwbeJKnbcj76qRPmNstabCDZuCbfaaMrz1BX3j+6DGj31fPOKMIhL+yoSLQunx5g4BiQVURrIbzxMErka5ljX+sl7P3FXnsqjJBfaPZ1lCYktDXG8OV7Uwnx9s0qwUTWpboD7ych5Nx4BCh1Z0ovviWHhvDn314RM8JjeKQ9T3d2jtdU4bVtjlbN9KCmHsuN3MsswoCQmvwuVKXkGRw== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=microsoft.com; s=arcselector10001; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-AntiSpam-MessageData-ChunkCount:X-MS-Exchange-AntiSpam-MessageData-0:X-MS-Exchange-AntiSpam-MessageData-1; bh=N/jY10/x4VHFfjWc0QrqSHAQjZ8rOTL96bcdTYZ/jos=; b=DH1wbD2NwHFufgXWmlnmU24s5B2c0SJg+hVi4SngpaPY8hL5xBzOUCFfCial6Qmylx7YTbviW98w+2SutMYUywx/5x9u1X7IhAyespqAyoHizngNFKn2/tVIb9ClgyZ4uG+/wKjSE/6FJ//7WcRQQk1ebjh1wn3Mrs4D8Fw5O+YWWS3zF5IR/1sWnTxeIPB/5cVJvauePkBVf04XC+Vgqdkx2+0r+SogslSVvrxQ3yp4OqatWSN4BNvJhjIvZBAFLrresrxRd2Z/O+uGQ3Vrt8bI8kkc+obq3B+9VmFeadqEECNrYxlE47h9cXu7Q1Nb2UfQPo7APkwqYqx9jwYd4g== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=pass smtp.mailfrom=u.nus.edu; dmarc=pass action=none header.from=u.nus.edu; dkim=pass header.d=u.nus.edu; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=u.nus.edu; s=selector2; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=N/jY10/x4VHFfjWc0QrqSHAQjZ8rOTL96bcdTYZ/jos=; b=Asra7pAOVLYeieadDd8DOKmPn5eAtdeRCizqctHuCOumqtt5o4qM/+veR4O1jgLhGur/fZAgbfViCmwSYAj4SogunC3DXOZ/alSGZBp7Hlwe7JFho0KLfYjnvEsQmRVXjfQK8M1LnySwWfZqYe78Pxm1nhPoasSNcAo1k0zNTYVXIf6zPELYDKnD1AzTGmi+5xwQgLnYlxgpCBbggTvGPXi8sHNKW6dbkLjIl8R6aH/JRN9lbHHVTe+X4EgflqJV7FIlJMSVk/Z1WZ5tZIjGl4/FdXXB/LWWGWtyFiMEDG2nxa5mUF0ifyqEfF8JidUGJ0xbPzjbLLMdtDjtQEf1rQ== Received: from SEZPR06MB6494.apcprd06.prod.outlook.com (2603:1096:101:18b::13) by TYSPR06MB6625.apcprd06.prod.outlook.com (2603:1096:400:47d::9) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.8207.11; Mon, 25 Nov 2024 20:54:36 +0000 Received: from SEZPR06MB6494.apcprd06.prod.outlook.com ([fe80::9c0b:dd8b:f453:59ec]) by SEZPR06MB6494.apcprd06.prod.outlook.com ([fe80::9c0b:dd8b:f453:59ec%4]) with mapi id 15.20.8207.010; Mon, 25 Nov 2024 20:54:36 +0000 From: Ba Jinsheng To: Andrei Lepikhov , "pgsql-performance@lists.postgresql.org" Subject: Performance of TPC-DS Query 95 Thread-Topic: Performance of TPC-DS Query 95 Thread-Index: AQHbP3t6Za5etgSb40yWWc87q8jgVA== Date: Mon, 25 Nov 2024 20:54:36 +0000 Message-ID: Accept-Language: en-US, en-SG Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: msip_labels: authentication-results: dkim=none (message not signed) header.d=none;dmarc=none action=none header.from=u.nus.edu; x-ms-publictraffictype: Email x-ms-traffictypediagnostic: SEZPR06MB6494:EE_|TYSPR06MB6625:EE_ x-ms-office365-filtering-correlation-id: b8ed7662-29fd-4e16-4f36-08dd0d935f01 x-ms-exchange-senderadcheck: 1 x-ms-exchange-antispam-relay: 0 x-microsoft-antispam: BCL:0;ARA:13230040|376014|1800799024|366016|8096899003|38070700018; x-microsoft-antispam-message-info: =?iso-8859-1?Q?stVxeWpZ/LkS5Ho7u86UVQX8FTjCOf86+7s/EljFlcmdQXsG3MhJHIwXKS?= =?iso-8859-1?Q?2ZLfUIMeMmqFepuQvnySza7Rq46xpkge0i+OpHTn74HJV3jDAr5qP4o/ZR?= =?iso-8859-1?Q?3UFGT3gMcXhXIsbkfLIG98yMZXgDg2sKz1xhxAc/qdz6lFylSo0BruaqrV?= =?iso-8859-1?Q?Mezxes6V9gNbejonyo5LxsWOo8ExY8p9Iw2A6r1yvNsTtpCOEqkfPa+uXQ?= =?iso-8859-1?Q?5CFYC98GBSyHQ+l/jRDAtXGZRxLdZAApwRQ2DKzhGMo1x3PDeseHDqczuo?= =?iso-8859-1?Q?iNbRem921Yh6+cb1hsefGBpnrN0LyA4BDhoQq5prHPjTsX8YqD40VpxguA?= =?iso-8859-1?Q?QTt/tdXcO9tScqz5zCbkRrjq8l0yEGuyCTeDr8uK9V6tj34r27d0xGpY4S?= =?iso-8859-1?Q?HVMlPv5cq4GYyxGev8pLEpVD8AMMb8czBXjvqbpnotUl1/UKPfcAJHlOSk?= =?iso-8859-1?Q?mFvdpG9ohtjYfM9mE3uaQ44N/4ROHrcWt6IHgZ3Q5nFOwiBgpL/hiqLBsR?= =?iso-8859-1?Q?NgP7rZIG9xt9dJIQ0svz3e2MIkpgxABzwDSfpp1UCccEy+Udyjwk1IhzC2?= =?iso-8859-1?Q?IekizyP2Zm1aYQg6bB+khoWLjsy0QqwXOsL7ZjRk8CFDKgMOkBJxRo4YhR?= =?iso-8859-1?Q?xTsQAR5zo9lf76x2JhQ27yI+l1iIXjnZkRFvv5RDH3O9LCXV1zqRpFRrXP?= =?iso-8859-1?Q?c5olaYaRmzi1lFyqhQN8Whf2npVv/IcpUoPYq5kiszuS6L5p2Z6Jb05jnN?= =?iso-8859-1?Q?NBN+EYR2nLe3TZImN1ijCCKEK5MEXOWal6LtH9sSLP2wlrheno6qS8nT9b?= =?iso-8859-1?Q?t9usJYtYdbuoG/37JBl1M4x8EcW77/qKf2bKkvTZ5Vzx8aLNo1KtmmYzQ0?= =?iso-8859-1?Q?HMUkHqTn0hzgQK4M72KtZboncfO55pWG9bCHh5EcDhLWL+KLPRyxGgcDfB?= =?iso-8859-1?Q?ldiIFw/SKsF93HcbaphMMiMiPP+/7ll9Avg5197xbVJT1zwBJtGgGYeKr1?= =?iso-8859-1?Q?TuwDgZ4b3YGY32aAaIgDd80wUnWmpeu0nZ92pAPUrbPaAr11HFvhryPn+b?= =?iso-8859-1?Q?++I49A9ZZHUCDehn4xqD0JchIr4kjV0rxk427Mt34TSibE5sgRIBVYREZe?= =?iso-8859-1?Q?BPjnHzvJ7KCv6x00TXIGB18jrHNxIQ7rx2lWkrvOvULOwt8Z/7a5cT8Xj7?= =?iso-8859-1?Q?4/YeCDK2FPA/DFtYfFEbkuoNwPf5umW7zWi7/BOVkbK50ImwjM7we9sC0U?= =?iso-8859-1?Q?jWYEqVRO4ByUy+FKA0N3x8IueD7IkfMX/rKgsaGftZlrv4T7iOZzw0WfFv?= =?iso-8859-1?Q?qV6yX4hTLLpTKIvO1WGuTTw7wzOKWnjm9Y6eT34GJ/59M5vlmUVufF22pY?= =?iso-8859-1?Q?RNFSDupXVcHYiQSgt2+Zho9sBTNigblxOoUDNhCxn4vPlcQ+7vkWM=3D?= x-forefront-antispam-report: CIP:255.255.255.255;CTRY:;LANG:en;SCL:1;SRV:;IPV:NLI;SFV:NSPM;H:SEZPR06MB6494.apcprd06.prod.outlook.com;PTR:;CAT:NONE;SFS:(13230040)(376014)(1800799024)(366016)(8096899003)(38070700018);DIR:OUT;SFP:1102; x-ms-exchange-antispam-messagedata-chunkcount: 1 x-ms-exchange-antispam-messagedata-0: =?iso-8859-1?Q?2TpQg7nF0X+fko9fcoChMXrmycLzvTxl/k5/cEtATgI5J5+EvNyTiiH6cM?= =?iso-8859-1?Q?yZ1YB2lRGZZFmyJ+WLQqjn05rUJUy2wl88yDkyWNqDtIiItdZv2xlhJBEz?= =?iso-8859-1?Q?Fmn5l1qe9UBJNRO/E9lPAIXoDEs/whUE96bbtw2INyHoZUDAWvRLQuKTZK?= =?iso-8859-1?Q?9vYFx4LNZGtQcqs1OCjdZXK+QNmE1xC6xCr9zij5WB4paLCswd3x3gI27g?= =?iso-8859-1?Q?FO81+lq5SuH+cL3a5Rcph1knQ1tsyrsDMJXCTVMe+OQHzN31Bg0o71t3Di?= =?iso-8859-1?Q?HEUljN2Mmh7ajqTBCVdQ55GOREJB7Q8iLnkYimy2a8rewymqjDHBVMNIfs?= =?iso-8859-1?Q?DIm7CFbWzpPiQZg4PsAGX33QcrbHBfyjM4gZTHroNUDY1Zm4sOhIkoIvEL?= =?iso-8859-1?Q?BfUuNVgULVzQhs/t33bot4ex9CqiLoxSvTkDObjAa9A4HGcrWEovzidyHk?= =?iso-8859-1?Q?9DplPWzk5aRGYqIdxl2kFrAO5QQQ7+/96+nVW523L7UGwFkVb9bkaHDxPj?= =?iso-8859-1?Q?sGujyRZpbzQRNCtAlxErADf5DxqEmn2DV0A3TAbJqWPCLW4cplK8o1BK1P?= =?iso-8859-1?Q?ceXAO551TmseNpJ3wIW+PpHuc3KSHAw3kvJAj/0eWK44nvE00k5xfY1910?= =?iso-8859-1?Q?uYfTaj4ASpw9nF1PEmxUsMzP/S0fx5Au754HUzxdrrXntvUoInKU4WtcHl?= =?iso-8859-1?Q?DKApFO4R9VrpVB8mp6h/nR9ROboOjSVZOrqttgQccbYOy84O3P3pwvcpSk?= =?iso-8859-1?Q?RC0lHkrbQAshfn5MdCK0eZ0ITvazITH/vY+FPA7nbSi59LPPd0hb2uOPA3?= =?iso-8859-1?Q?ValyTOEQDXIdWH5D+mOEKOsiUf5MGdZKtuDT/J69ErH8JcjcLnkOCdHky6?= =?iso-8859-1?Q?vB9wbPXvHBW3O3ApBtaDZQZHc2uFuQJBXhXPcqQfZA82b9481DTDkKqxT4?= =?iso-8859-1?Q?6lnr7gxFq2B/8NC/qI64boO5MVFeWqHU9v98RQaJy7p+2gqgrAMkRkB25j?= =?iso-8859-1?Q?Xc3n2V3+DjCAg4snkOe9DDJHbnEl8ZcSTs+UTcwE/9S0ganhb6ZmkpI5S8?= =?iso-8859-1?Q?sRoMOkZbW9z6nSMBS4z/AVx41rhYQRyiSSNpiaqKnYioPP1v7vkULJdH+O?= =?iso-8859-1?Q?Vc6ZAm4UHGBQKGE3xv0JOsiezQzo4m88qN/UuNslyWvbA/t+e85UclhxNk?= =?iso-8859-1?Q?Ilo1D+7nEuRdPoUS08sa4tQFZ0WBQOZzb/Vxf7xVB2i4rmdQUZup87gweN?= =?iso-8859-1?Q?ygd+8GRdn444XpVfwSEG802XfqQMBSpATE0qWDyYGfV/c0Di0Y0Em8NCCy?= =?iso-8859-1?Q?CtiR0g8H4iKWstY6/BgjQrF/xiXGV+1CiUSfzM2fZ+7Z3Jy7NetI5waHWU?= =?iso-8859-1?Q?fflfvL4k27dZbvLBRTD+c2zoTBAUh1qDI8cn9tKqrFdSsSZWTpg3oxBZfr?= =?iso-8859-1?Q?w6qKUgoRncrjW5vD3/oo8TGVAscLe6KaVCYF6LGvoNc8QPgKYYgHJtt4iJ?= =?iso-8859-1?Q?/VaEwuFUAzt+MW6fHkCqbvg/skBaaCck68Jfb7UC7woms7qW+1T5nZMpLV?= =?iso-8859-1?Q?st835qi04/t746EGA/iDKpXdYT0p0MOAqDXM3trj+MTkirz+zYmPVhPmB4?= =?iso-8859-1?Q?MhGJf1U/ls9ug=3D?= Content-Type: multipart/alternative; boundary="_000_SEZPR06MB64941B811E7F9EE81978367E8A2E2SEZPR06MB6494apcp_" MIME-Version: 1.0 X-OriginatorOrg: u.nus.edu X-MS-Exchange-CrossTenant-AuthAs: Internal X-MS-Exchange-CrossTenant-AuthSource: SEZPR06MB6494.apcprd06.prod.outlook.com X-MS-Exchange-CrossTenant-Network-Message-Id: b8ed7662-29fd-4e16-4f36-08dd0d935f01 X-MS-Exchange-CrossTenant-originalarrivaltime: 25 Nov 2024 20:54:36.1241 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: 5ba5ef5e-3109-4e77-85bd-cfeb0d347e82 X-MS-Exchange-CrossTenant-mailboxtype: HOSTED X-MS-Exchange-CrossTenant-userprincipalname: IePOs9HS3VCqtvWx89TsjJctnOQJaLk6fJ2WHXlCNRkYCMcBCByySElEsKFdZ0znDPO7CgwJLhVYDqStrB7yZQ== X-MS-Exchange-Transport-CrossTenantHeadersStamped: TYSPR06MB6625 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_SEZPR06MB64941B811E7F9EE81978367E8A2E2SEZPR06MB6494apcp_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi all, Please see this case. TPC-DS query 95: with ws_wh as (select ws1.ws_order_number,ws1.ws_warehouse_sk wh1,ws2.ws_warehouse_sk wh2 from web_sales ws1,web_sales ws2 where ws1.ws_order_number =3D ws2.ws_order_number and ws1.ws_warehouse_sk <> ws2.ws_warehouse_sk) select count(distinct ws_order_number) as "order count" ,sum(ws_ext_ship_cost) as "total shipping cost" ,sum(ws_net_profit) as "total net profit" from web_sales ws1 ,date_dim ,customer_address ,web_site where d_date between '1999-5-01' and (cast('1999-5-01' as date) + interval '60 days') and ws1.ws_ship_date_sk =3D d_date_sk and ws1.ws_ship_addr_sk =3D ca_address_sk and ca_state =3D 'TX' and ws1.ws_web_site_sk =3D web_site_sk and web_company_name =3D 'pri' and ws1.ws_order_number in (select ws_order_number from ws_wh) and ws1.ws_order_number in (select wr_order_number from web_returns,ws_wh where wr_order_number =3D ws_wh.ws_order_number= ) order by count(distinct ws_order_number) limit 100; Its execution time is nearly 1 min: = QUERY PLAN ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= -------------------------------------- Limit (cost=3D771620.21..771620.21 rows=3D1 width=3D72) (actual time=3D56= 669.478..56669.563 rows=3D1 loops=3D1) CTE ws_wh -> Hash Join (cost=3D37772.14..198810.77 rows=3D7242361 width=3D12) = (actual time=3D211.161..1443.926 rows=3D6644004 loops=3D1) Hash Cond: (ws1_1.ws_order_number =3D ws2.ws_order_number) Join Filter: (ws1_1.ws_warehouse_sk <> ws2.ws_warehouse_sk) Rows Removed by Join Filter: 2381030 -> Seq Scan on web_sales ws1_1 (cost=3D0.00..25968.84 rows=3D7= 19384 width=3D8) (actual time=3D0.014..106.870 rows=3D719384 loops=3D1) -> Hash (cost=3D25968.84..25968.84 rows=3D719384 width=3D8) (a= ctual time=3D210.247..210.248 rows=3D719384 loops=3D1) Buckets: 262144 Batches: 8 Memory Usage: 5563kB -> Seq Scan on web_sales ws2 (cost=3D0.00..25968.84 rows= =3D719384 width=3D8) (actual time=3D0.005..111.802 rows=3D719384 loops=3D1) -> Sort (cost=3D572809.44..572809.45 rows=3D1 width=3D72) (actual time= =3D56669.477..56669.559 rows=3D1 loops=3D1) Sort Key: (count(DISTINCT ws1.ws_order_number)) Sort Method: quicksort Memory: 25kB -> Aggregate (cost=3D572809.42..572809.43 rows=3D1 width=3D72) (= actual time=3D56669.456..56669.538 rows=3D1 loops=3D1) -> Sort (cost=3D572809.37..572809.38 rows=3D5 width=3D16) = (actual time=3D56669.424..56669.510 rows=3D121 loops=3D1) Sort Key: ws1.ws_order_number Sort Method: quicksort Memory: 29kB -> Nested Loop Semi Join (cost=3D390001.60..572809.3= 1 rows=3D5 width=3D16) (actual time=3D5814.554..56669.277 rows=3D121 loops= =3D1) Join Filter: (ws1.ws_order_number =3D ws_wh.ws_o= rder_number) Rows Removed by Join Filter: 400808138 -> Hash Join (cost=3D390001.60..414560.96 rows= =3D5 width=3D24) (actual time=3D4939.833..4940.928 rows=3D121 loops=3D1) Hash Cond: (ws1.ws_order_number =3D web_re= turns.wr_order_number) -> Gather (cost=3D1003.03..25562.31 rows= =3D8 width=3D16) (actual time=3D2.891..3.674 rows=3D148 loops=3D1) Workers Planned: 2 Workers Launched: 2 -> Nested Loop (cost=3D3.03..24561= .51 rows=3D3 width=3D16) (actual time=3D4.531..75.030 rows=3D49 loops=3D3) -> Nested Loop (cost=3D2.74.= .24548.21 rows=3D42 width=3D20) (actual time=3D1.566..72.683 rows=3D584 loo= ps=3D3) -> Hash Join (cost=3D2= .44..22672.82 rows=3D49957 width=3D24) (actual time=3D0.158..58.416 rows=3D= 31830 loops=3D3) Hash Cond: (ws1.ws= _web_site_sk =3D web_site.web_site_sk) -> Parallel Seq S= can on web_sales ws1 (cost=3D0.00..21772.43 rows=3D299743 width=3D28) (act= ual time=3D0.054..24.308 rows=3D239795 loops=3D3) -> Hash (cost=3D= 2.38..2.38 rows=3D5 width=3D4) (actual time=3D0.047..0.047 rows=3D5 loops= =3D3) Buckets: 102= 4 Batches: 1 Memory Usage: 9kB -> Seq Scan= on web_site (cost=3D0.00..2.38 rows=3D5 width=3D4) (actual time=3D0.036..= 0.042 rows=3D5 loops=3D3) Filter= : (web_company_name =3D 'pri'::bpchar) Rows R= emoved by Filter: 25 -> Memoize (cost=3D0.3= 0..0.33 rows=3D1 width=3D4) (actual time=3D0.000..0.000 rows=3D0 loops=3D95= 491) Cache Key: ws1.ws_= ship_date_sk Cache Mode: logica= l Hits: 67 Misses: = 413 Evictions: 0 Overflows: 0 Memory Usage: 28kB Worker 0: Hits: 4= 4145 Misses: 1934 Evictions: 0 Overflows: 0 Memory Usage: 131kB Worker 1: Hits: 4= 6993 Misses: 1939 Evictions: 0 Overflows: 0 Memory Usage: 131kB -> Index Scan usi= ng date_dim_pkey on date_dim (cost=3D0.29..0.32 rows=3D1 width=3D4) (actua= l time=3D0.003..0.003 rows=3D0 loops=3D4286) Index Cond: = (d_date_sk =3D ws1.ws_ship_date_sk) Filter: ((d_= date >=3D '1999-05-01'::date) AND (d_date <=3D '1999-06-30 00:00:00'::times= tamp without time zone)) Rows Removed= by Filter: 1 -> Index Scan using customer_= address_pkey on customer_address (cost=3D0.29..0.32 rows=3D1 width=3D4) (a= ctual time=3D0.004..0.004 rows=3D0 loops=3D1752) Index Cond: (ca_address_= sk =3D ws1.ws_ship_addr_sk) Filter: (ca_state =3D 'T= X'::bpchar) Rows Removed by Filter: = 1 -> Hash (cost=3D388535.46..388535.46 row= s=3D37049 width=3D8) (actual time=3D4936.733..4936.734 rows=3D42249 loops= =3D1) Buckets: 65536 Batches: 1 Memory U= sage: 2163kB -> HashAggregate (cost=3D388164.97= ..388535.46 rows=3D37049 width=3D8) (actual time=3D4926.772..4931.933 rows= =3D42249 loops=3D1) Group Key: web_returns.wr_orde= r_number Batches: 1 Memory Usage: 3345= kB -> Hash Join (cost=3D2942.67= ..365438.21 rows=3D9090701 width=3D8) (actual time=3D230.033..4014.732 rows= =3D8677946 loops=3D1) Hash Cond: (ws_wh_1.ws_o= rder_number =3D web_returns.wr_order_number) -> CTE Scan on ws_wh ws= _wh_1 (cost=3D0.00..144847.22 rows=3D7242361 width=3D4) (actual time=3D211= .163..2765.479 rows=3D6644004 loops=3D1) -> Hash (cost=3D2045.6= 3..2045.63 rows=3D71763 width=3D4) (actual time=3D18.445..18.445 rows=3D717= 63 loops=3D1) Buckets: 131072 B= atches: 1 Memory Usage: 3547kB -> Seq Scan on we= b_returns (cost=3D0.00..2045.63 rows=3D71763 width=3D4) (actual time=3D0.0= 25..10.838 rows=3D71763 loops=3D1) -> CTE Scan on ws_wh (cost=3D0.00..144847.22 r= ows=3D7242361 width=3D4) (actual time=3D0.002..232.953 rows=3D3312465 loops= =3D121) Planning Time: 2.967 ms Execution Time: 56689.671 ms (63 rows) If applying this patch: diff --git a/src/backend/optimizer/plan/analyzejoins.c b/src/backend/optimi= zer/plan/analyzejoins.c index c3fd4a81f8..c99282cda6 100644 --- a/src/backend/optimizer/plan/analyzejoins.c +++ b/src/backend/optimizer/plan/analyzejoins.c @@ -1231,7 +1231,7 @@ innerrel_is_unique(PlannerInfo *root, } /* No cached information, so try to make the proof. */ - if (is_innerrel_unique_for(root, joinrelids, outerrelids, innerrel, + if (!is_innerrel_unique_for(root, joinrelids, outerrelids, innerrel= , jointype, restri= ctlist)) { /* The execution time is reduced to 6 seconds: = QUERY PLAN ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= -------------------------------------------- Limit (cost=3D441755.76..441755.77 rows=3D1 width=3D72) (actual time=3D65= 08.013..6508.256 rows=3D1 loops=3D1) CTE ws_wh -> Hash Join (cost=3D37772.14..74062.53 rows=3D7095248 width=3D12) (= actual time=3D203.407..560.264 rows=3D719205 loops=3D1) Hash Cond: (ws1_1.ws_order_number =3D ws2.ws_order_number) Join Filter: (ws1_1.ws_warehouse_sk <> ws2.ws_warehouse_sk) Rows Removed by Join Filter: 255623 -> Seq Scan on web_sales ws1_1 (cost=3D0.00..25968.84 rows=3D7= 19384 width=3D8) (actual time=3D0.021..95.808 rows=3D719384 loops=3D1) -> Hash (cost=3D25968.84..25968.84 rows=3D719384 width=3D8) (a= ctual time=3D202.456..202.457 rows=3D719384 loops=3D1) Buckets: 262144 Batches: 8 Memory Usage: 5563kB -> Seq Scan on web_sales ws2 (cost=3D0.00..25968.84 rows= =3D719384 width=3D8) (actual time=3D0.017..105.868 rows=3D719384 loops=3D1) -> Sort (cost=3D367693.24..367693.24 rows=3D1 width=3D72) (actual time= =3D6508.012..6508.252 rows=3D1 loops=3D1) Sort Key: (count(DISTINCT ws1.ws_order_number)) Sort Method: quicksort Memory: 25kB -> Aggregate (cost=3D367693.22..367693.23 rows=3D1 width=3D72) (= actual time=3D6507.989..6508.230 rows=3D1 loops=3D1) -> Sort (cost=3D367693.16..367693.18 rows=3D5 width=3D16) = (actual time=3D6507.943..6508.189 rows=3D121 loops=3D1) Sort Key: ws1.ws_order_number Sort Method: quicksort Memory: 29kB -> Nested Loop Semi Join (cost=3D189126.19..367693.1= 1 rows=3D5 width=3D16) (actual time=3D998.191..6508.088 rows=3D121 loops=3D= 1) Join Filter: (ws1.ws_order_number =3D ws_wh.ws_o= rder_number) Rows Removed by Join Filter: 43344728 -> Nested Loop (cost=3D189126.19..212112.41 ro= ws=3D5 width=3D24) (actual time=3D909.308..911.031 rows=3D121 loops=3D1) Join Filter: (web_site.web_site_sk =3D ws1= .ws_web_site_sk) Rows Removed by Join Filter: 4359 -> Hash Join (cost=3D189126.19..212107.8= 4 rows=3D29 width=3D28) (actual time=3D909.243..910.378 rows=3D896 loops=3D= 1) Hash Cond: (ws1.ws_order_number =3D = web_returns.wr_order_number) -> Gather (cost=3D3050.28..26031.4= 9 rows=3D45 width=3D20) (actual time=3D10.506..11.281 rows=3D1103 loops=3D1= ) Workers Planned: 2 Workers Launched: 2 -> Nested Loop (cost=3D2050.= 28..25026.99 rows=3D19 width=3D20) (actual time=3D8.162..64.689 rows=3D368 = loops=3D3) -> Parallel Hash Join = (cost=3D2049.99..24947.90 rows=3D242 width=3D24) (actual time=3D6.293..52.3= 76 rows=3D4465 loops=3D3) Hash Cond: (ws1.ws= _ship_date_sk =3D date_dim.d_date_sk) -> Parallel Seq S= can on web_sales ws1 (cost=3D0.00..21772.43 rows=3D299743 width=3D28) (act= ual time=3D0.024..24.598 rows=3D239795 loops=3D3) -> Parallel Hash = (cost=3D2049.55..2049.55 rows=3D35 width=3D4) (actual time=3D5.895..5.896 = rows=3D20 loops=3D3) Buckets: 102= 4 Batches: 1 Memory Usage: 40kB -> Parallel= Seq Scan on date_dim (cost=3D0.00..2049.55 rows=3D35 width=3D4) (actual t= ime=3D4.855..5.816 rows=3D20 loops=3D3) Filter= : ((d_date >=3D '1999-05-01'::date) AND (d_date <=3D '1999-06-30 00:00:00':= :timestamp without time zone)) Rows R= emoved by Filter: 24329 -> Index Scan using cus= tomer_address_pkey on customer_address (cost=3D0.29..0.32 rows=3D1 width= =3D4) (actual time=3D0.003..0.003 rows=3D0 loops=3D13394) Index Cond: (ca_ad= dress_sk =3D ws1.ws_ship_addr_sk) Filter: (ca_state = =3D 'TX'::bpchar) Rows Removed by Fi= lter: 1 -> Hash (cost=3D185612.75..185612.= 75 rows=3D37053 width=3D8) (actual time=3D898.519..898.521 rows=3D42249 loo= ps=3D1) Buckets: 65536 Batches: 1 Me= mory Usage: 2163kB -> HashAggregate (cost=3D185= 242.22..185612.75 rows=3D37053 width=3D8) (actual time=3D888.235..893.423 r= ows=3D42249 loops=3D1) Group Key: web_returns.w= r_order_number Batches: 1 Memory Usage= : 3345kB -> Hash Join (cost=3D2= 942.67..163474.00 rows=3D8707289 width=3D8) (actual time=3D223.693..825.681= rows=3D518567 loops=3D1) Hash Cond: (ws_wh_= 1.ws_order_number =3D web_returns.wr_order_number) -> CTE Scan on ws= _wh ws_wh_1 (cost=3D0.00..141904.96 rows=3D7095248 width=3D4) (actual time= =3D203.411..706.045 rows=3D719205 loops=3D1) -> Hash (cost=3D= 2045.63..2045.63 rows=3D71763 width=3D4) (actual time=3D19.983..19.983 rows= =3D71763 loops=3D1) Buckets: 131= 072 Batches: 1 Memory Usage: 3547kB -> Seq Scan= on web_returns (cost=3D0.00..2045.63 rows=3D71763 width=3D4) (actual time= =3D0.026..12.516 rows=3D71763 loops=3D1) -> Materialize (cost=3D0.00..2.40 rows= =3D5 width=3D4) (actual time=3D0.000..0.000 rows=3D5 loops=3D896) -> Seq Scan on web_site (cost=3D0.= 00..2.38 rows=3D5 width=3D4) (actual time=3D0.046..0.052 rows=3D5 loops=3D1= ) Filter: (web_company_name =3D = 'pri'::bpchar) Rows Removed by Filter: 25 -> CTE Scan on ws_wh (cost=3D0.00..141904.96 r= ows=3D7095248 width=3D4) (actual time=3D0.001..25.301 rows=3D358222 loops= =3D121) Planning Time: 3.432 ms Execution Time: 6512.766 ms (59 rows) The difference between both query plans is the second one uses Materialize = instead of Memoize. From the code, it seems that changing the usage of the = cache brings performance improvement unexpectedly. Environment: For the benchmark, I used 1 GB data, and my entire data folder can be downl= oaded here: https://drive.google.com/file/d/1iK5gfyKudfn2BczpoZbNRY_IAD_rIT= Zu/view?usp=3Dsharing The connection string is: postgresql://ubuntu:ubuntu(at)127(dot)0(dot)0(dot= )1:5432/tpcds" tpch=3D# select version(); version ---------------------------------------------------------------------------= ----------------------- PostgreSQL 17.0 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.2.0-23u= buntu4) 13.2.0, 64-bit (1 row) Best regards, Jinsheng Ba Notice: This email is generated from the account of an NUS alumnus. Content= s, views, and opinions therein are solely those of the sender. --_000_SEZPR06MB64941B811E7F9EE81978367E8A2E2SEZPR06MB6494apcp_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi all,

Please see this case.

TPC-DS query 95:
with ws_wh as
(select ws1.ws_order_number,ws1.ws_warehouse_sk wh1,ws2.ws_warehouse_sk wh2=
 from web_sales ws1,web_sales ws2
 where ws1.ws_order_number =3D ws2.ws_order_number
   and ws1.ws_warehouse_sk <> ws2.ws_warehouse_sk)
 select  
   count(distinct ws_order_number) as "order count"
  ,sum(ws_ext_ship_cost) as "total shipping cost"
  ,sum(ws_net_profit) as "total net profit"
from
   web_sales ws1
  ,date_dim
  ,customer_address
  ,web_site
where
    d_date between '1999-5-01' and
           (cast('1999-5-01' as date) + inter= val '60 days')
and ws1.ws_ship_date_sk =3D d_date_sk
and ws1.ws_ship_addr_sk =3D ca_address_sk
and ca_state =3D 'TX'
and ws1.ws_web_site_sk =3D web_site_sk
and web_company_name =3D 'pri'
and ws1.ws_order_number in (select ws_order_number
                     = ;       from ws_wh)
and ws1.ws_order_number in (select wr_order_number
                     = ;       from web_returns,ws_wh
                     = ;       where wr_order_number =3D ws_wh.ws_order_number)
order by count(distinct ws_order_number)
limit 100;


Its execution time is nearly 1 min:

                     = ;                     &nb= sp;                     &= nbsp;                    =    QUERY PLAN               &= nbsp;                    =                     &nbs= p;                     &n= bsp;        
---------------------------------------------------------------------------= ---------------------------------------------------------------------------= --------------------------------------
 Limit  (cost=3D771620.21..771620.21 rows=3D1 width=3D72) (actual= time=3D56669.478..56669.563 rows=3D1 loops=3D1)
   CTE ws_wh
     ->  Hash Join  (cost=3D37772.14..198810.77= rows=3D7242361 width=3D12) (actual time=3D211.161..1443.926 rows=3D6644004= loops=3D1)
           Hash Cond: (ws1_1.ws_order_number = =3D ws2.ws_order_number)
           Join Filter: (ws1_1.ws_warehouse_s= k <> ws2.ws_warehouse_sk)
           Rows Removed by Join Filter: 23810= 30
           ->  Seq Scan on web_sales = ws1_1  (cost=3D0.00..25968.84 rows=3D719384 width=3D8) (actual time=3D= 0.014..106.870 rows=3D719384 loops=3D1)
           ->  Hash  (cost=3D259= 68.84..25968.84 rows=3D719384 width=3D8) (actual time=3D210.247..210.248 ro= ws=3D719384 loops=3D1)
                 Buckets: 2621= 44  Batches: 8  Memory Usage: 5563kB
                 ->  S= eq Scan on web_sales ws2  (cost=3D0.00..25968.84 rows=3D719384 width= =3D8) (actual time=3D0.005..111.802 rows=3D719384 loops=3D1)
   ->  Sort  (cost=3D572809.44..572809.45 rows=3D1 w= idth=3D72) (actual time=3D56669.477..56669.559 rows=3D1 loops=3D1)
         Sort Key: (count(DISTINCT ws1.ws_order_nu= mber))
         Sort Method: quicksort  Memory: 25kB=
         ->  Aggregate  (cost=3D57280= 9.42..572809.43 rows=3D1 width=3D72) (actual time=3D56669.456..56669.538 ro= ws=3D1 loops=3D1)
               ->  Sort &nb= sp;(cost=3D572809.37..572809.38 rows=3D5 width=3D16) (actual time=3D56669.4= 24..56669.510 rows=3D121 loops=3D1)
                     = ;Sort Key: ws1.ws_order_number
                     = ;Sort Method: quicksort  Memory: 29kB
                     = ;->  Nested Loop Semi Join  (cost=3D390001.60..572809.31 rows= =3D5 width=3D16) (actual time=3D5814.554..56669.277 rows=3D121 loops=3D1)
                     = ;      Join Filter: (ws1.ws_order_number =3D ws_wh.ws_order_= number)
                     = ;      Rows Removed by Join Filter: 400808138
                     = ;      ->  Hash Join  (cost=3D390001.60..414560= .96 rows=3D5 width=3D24) (actual time=3D4939.833..4940.928 rows=3D121 loops= =3D1)
                     = ;            Hash Cond: (ws1.ws_order_number = =3D web_returns.wr_order_number)
                     = ;            ->  Gather  (cost= =3D1003.03..25562.31 rows=3D8 width=3D16) (actual time=3D2.891..3.674 rows= =3D148 loops=3D1)
                     = ;                  Workers Pla= nned: 2
                     = ;                  Workers Lau= nched: 2
                     = ;                  ->  = ;Nested Loop  (cost=3D3.03..24561.51 rows=3D3 width=3D16) (actual time= =3D4.531..75.030 rows=3D49 loops=3D3)
                     = ;                     &nb= sp;  ->  Nested Loop  (cost=3D2.74..24548.21 rows=3D42 wi= dth=3D20) (actual time=3D1.566..72.683 rows=3D584 loops=3D3)
                     = ;                     &nb= sp;        ->  Hash Join  (cost=3D2.44..22= 672.82 rows=3D49957 width=3D24) (actual time=3D0.158..58.416 rows=3D31830 l= oops=3D3)
                     = ;                     &nb= sp;              Hash Cond: (ws1.ws_web_= site_sk =3D web_site.web_site_sk)
                     = ;                     &nb= sp;              ->  Parallel Se= q Scan on web_sales ws1  (cost=3D0.00..21772.43 rows=3D299743 width=3D= 28) (actual time=3D0.054..24.308 rows=3D239795 loops=3D3)
                     = ;                     &nb= sp;              ->  Hash  = (cost=3D2.38..2.38 rows=3D5 width=3D4) (actual time=3D0.047..0.047 rows=3D5= loops=3D3)
                     = ;                     &nb= sp;                    Bu= ckets: 1024  Batches: 1  Memory Usage: 9kB
                     = ;                     &nb= sp;                    -&= gt;  Seq Scan on web_site  (cost=3D0.00..2.38 rows=3D5 width=3D4)= (actual time=3D0.036..0.042 rows=3D5 loops=3D3)
                     = ;                     &nb= sp;                     &= nbsp;    Filter: (web_company_name =3D 'pri'::bpchar)
                     = ;                     &nb= sp;                     &= nbsp;    Rows Removed by Filter: 25
                     = ;                     &nb= sp;        ->  Memoize  (cost=3D0.30..0.33= rows=3D1 width=3D4) (actual time=3D0.000..0.000 rows=3D0 loops=3D95491)
                     = ;                     &nb= sp;              Cache Key: ws1.ws_ship_= date_sk
                     = ;                     &nb= sp;              Cache Mode: logical
                     = ;                     &nb= sp;              Hits: 67  Misses: = 413  Evictions: 0  Overflows: 0  Memory Usage: 28kB
                     = ;                     &nb= sp;              Worker 0:  Hits: 4= 4145  Misses: 1934  Evictions: 0  Overflows: 0  Memory = Usage: 131kB
                     = ;                     &nb= sp;              Worker 1:  Hits: 4= 6993  Misses: 1939  Evictions: 0  Overflows: 0  Memory = Usage: 131kB
                     = ;                     &nb= sp;              ->  Index Scan = using date_dim_pkey on date_dim  (cost=3D0.29..0.32 rows=3D1 width=3D4= ) (actual time=3D0.003..0.003 rows=3D0 loops=3D4286)
                     = ;                     &nb= sp;                    In= dex Cond: (d_date_sk =3D ws1.ws_ship_date_sk)
                     = ;                     &nb= sp;                    Fi= lter: ((d_date >=3D '1999-05-01'::date) AND (d_date <=3D '1999-06-30 = 00:00:00'::timestamp without time zone))
                     = ;                     &nb= sp;                    Ro= ws Removed by Filter: 1
                     = ;                     &nb= sp;  ->  Index Scan using customer_address_pkey on customer_ad= dress  (cost=3D0.29..0.32 rows=3D1 width=3D4) (actual time=3D0.004..0.= 004 rows=3D0 loops=3D1752)
                     = ;                     &nb= sp;        Index Cond: (ca_address_sk =3D ws1.ws_ship_a= ddr_sk)
                     = ;                     &nb= sp;        Filter: (ca_state =3D 'TX'::bpchar)
                     = ;                     &nb= sp;        Rows Removed by Filter: 1
                     = ;            ->  Hash  (cost=3D3= 88535.46..388535.46 rows=3D37049 width=3D8) (actual time=3D4936.733..4936.7= 34 rows=3D42249 loops=3D1)
                     = ;                  Buckets: 65= 536  Batches: 1  Memory Usage: 2163kB
                     = ;                  ->  = ;HashAggregate  (cost=3D388164.97..388535.46 rows=3D37049 width=3D8) (= actual time=3D4926.772..4931.933 rows=3D42249 loops=3D1)
                     = ;                     &nb= sp;  Group Key: web_returns.wr_order_number
                     = ;                     &nb= sp;  Batches: 1  Memory Usage: 3345kB
                     = ;                     &nb= sp;  ->  Hash Join  (cost=3D2942.67..365438.21 rows=3D909= 0701 width=3D8) (actual time=3D230.033..4014.732 rows=3D8677946 loops=3D1)<= /div>
                     = ;                     &nb= sp;        Hash Cond: (ws_wh_1.ws_order_number =3D web_= returns.wr_order_number)
                     = ;                     &nb= sp;        ->  CTE Scan on ws_wh ws_wh_1  = (cost=3D0.00..144847.22 rows=3D7242361 width=3D4) (actual time=3D211.163..2= 765.479 rows=3D6644004 loops=3D1)
                     = ;                     &nb= sp;        ->  Hash  (cost=3D2045.63..2045= .63 rows=3D71763 width=3D4) (actual time=3D18.445..18.445 rows=3D71763 loop= s=3D1)
                     = ;                     &nb= sp;              Buckets: 131072  B= atches: 1  Memory Usage: 3547kB
                     = ;                     &nb= sp;              ->  Seq Scan on= web_returns  (cost=3D0.00..2045.63 rows=3D71763 width=3D4) (actual ti= me=3D0.025..10.838 rows=3D71763 loops=3D1)
                     = ;      ->  CTE Scan on ws_wh  (cost=3D0.00..144= 847.22 rows=3D7242361 width=3D4) (actual time=3D0.002..232.953 rows=3D33124= 65 loops=3D121)
 Planning Time: 2.967 ms
 Execution Time: 56689.671 ms
(63 rows)


If applying this patch:

diff --git a/src/backend/optimizer/plan/analyzejoins.c b/src/backend/optimi= zer/plan/analyzejoins.c
index c3fd4a81f8..c99282cda6 100644
--- a/src/backend/optimizer/plan/analyzejoins.c
+++ b/src/backend/optimizer/plan/analyzejoins.c
@@ -1231,7 +1231,7 @@ innerrel_is_unique(PlannerInfo *root,
        }
 
        /* No cached information, so try to make the pr= oof. */
-       if (is_innerrel_unique_for(root, joinrelids, outerre= lids, innerrel,
+       if (!is_innerrel_unique_for(root, joinrelids, outerr= elids, innerrel,
                     = ;                     &nb= sp;                jointype, restri= ctlist))
        {
                /*

The execution time is reduced to 6 seconds:

                     = ;                     &nb= sp;                     &= nbsp;                    =       QUERY PLAN             =                      = ;                     &nb= sp;                     &= nbsp;              
---------------------------------------------------------------------------= ---------------------------------------------------------------------------= --------------------------------------------
 Limit  (cost=3D441755.76..441755.77 rows=3D1 width=3D72) (actual= time=3D6508.013..6508.256 rows=3D1 loops=3D1)
   CTE ws_wh
     ->  Hash Join  (cost=3D37772.14..74062.53 = rows=3D7095248 width=3D12) (actual time=3D203.407..560.264 rows=3D719205 lo= ops=3D1)
           Hash Cond: (ws1_1.ws_order_number = =3D ws2.ws_order_number)
           Join Filter: (ws1_1.ws_warehouse_s= k <> ws2.ws_warehouse_sk)
           Rows Removed by Join Filter: 25562= 3
           ->  Seq Scan on web_sales = ws1_1  (cost=3D0.00..25968.84 rows=3D719384 width=3D8) (actual time=3D= 0.021..95.808 rows=3D719384 loops=3D1)
           ->  Hash  (cost=3D259= 68.84..25968.84 rows=3D719384 width=3D8) (actual time=3D202.456..202.457 ro= ws=3D719384 loops=3D1)
                 Buckets: 2621= 44  Batches: 8  Memory Usage: 5563kB
                 ->  S= eq Scan on web_sales ws2  (cost=3D0.00..25968.84 rows=3D719384 width= =3D8) (actual time=3D0.017..105.868 rows=3D719384 loops=3D1)
   ->  Sort  (cost=3D367693.24..367693.24 rows=3D1 w= idth=3D72) (actual time=3D6508.012..6508.252 rows=3D1 loops=3D1)
         Sort Key: (count(DISTINCT ws1.ws_order_nu= mber))
         Sort Method: quicksort  Memory: 25kB=
         ->  Aggregate  (cost=3D36769= 3.22..367693.23 rows=3D1 width=3D72) (actual time=3D6507.989..6508.230 rows= =3D1 loops=3D1)
               ->  Sort &nb= sp;(cost=3D367693.16..367693.18 rows=3D5 width=3D16) (actual time=3D6507.94= 3..6508.189 rows=3D121 loops=3D1)
                     = ;Sort Key: ws1.ws_order_number
                     = ;Sort Method: quicksort  Memory: 29kB
                     = ;->  Nested Loop Semi Join  (cost=3D189126.19..367693.11 rows= =3D5 width=3D16) (actual time=3D998.191..6508.088 rows=3D121 loops=3D1)
                     = ;      Join Filter: (ws1.ws_order_number =3D ws_wh.ws_order_= number)
                     = ;      Rows Removed by Join Filter: 43344728
                     = ;      ->  Nested Loop  (cost=3D189126.19..2121= 12.41 rows=3D5 width=3D24) (actual time=3D909.308..911.031 rows=3D121 loops= =3D1)
                     = ;            Join Filter: (web_site.web_site_= sk =3D ws1.ws_web_site_sk)
                     = ;            Rows Removed by Join Filter: 435= 9
                     = ;            ->  Hash Join  (cos= t=3D189126.19..212107.84 rows=3D29 width=3D28) (actual time=3D909.243..910.= 378 rows=3D896 loops=3D1)
                     = ;                  Hash Cond: = (ws1.ws_order_number =3D web_returns.wr_order_number)
                     = ;                  ->  = ;Gather  (cost=3D3050.28..26031.49 rows=3D45 width=3D20) (actual time= =3D10.506..11.281 rows=3D1103 loops=3D1)
                     = ;                     &nb= sp;  Workers Planned: 2
                     = ;                     &nb= sp;  Workers Launched: 2
                     = ;                     &nb= sp;  ->  Nested Loop  (cost=3D2050.28..25026.99 rows=3D19= width=3D20) (actual time=3D8.162..64.689 rows=3D368 loops=3D3)
                     = ;                     &nb= sp;        ->  Parallel Hash Join  (cost= =3D2049.99..24947.90 rows=3D242 width=3D24) (actual time=3D6.293..52.376 ro= ws=3D4465 loops=3D3)
                     = ;                     &nb= sp;              Hash Cond: (ws1.ws_ship= _date_sk =3D date_dim.d_date_sk)
                     = ;                     &nb= sp;              ->  Parallel Se= q Scan on web_sales ws1  (cost=3D0.00..21772.43 rows=3D299743 width=3D= 28) (actual time=3D0.024..24.598 rows=3D239795 loops=3D3)
                     = ;                     &nb= sp;              ->  Parallel Ha= sh  (cost=3D2049.55..2049.55 rows=3D35 width=3D4) (actual time=3D5.895= ..5.896 rows=3D20 loops=3D3)
                     = ;                     &nb= sp;                    Bu= ckets: 1024  Batches: 1  Memory Usage: 40kB
                     = ;                     &nb= sp;                    -&= gt;  Parallel Seq Scan on date_dim  (cost=3D0.00..2049.55 rows=3D= 35 width=3D4) (actual time=3D4.855..5.816 rows=3D20 loops=3D3)
                     = ;                     &nb= sp;                     &= nbsp;    Filter: ((d_date >=3D '1999-05-01'::date) AND (d_date= <=3D '1999-06-30 00:00:00'::timestamp without time zone))
                     = ;                     &nb= sp;                     &= nbsp;    Rows Removed by Filter: 24329
                     = ;                     &nb= sp;        ->  Index Scan using customer_addres= s_pkey on customer_address  (cost=3D0.29..0.32 rows=3D1 width=3D4) (ac= tual time=3D0.003..0.003 rows=3D0 loops=3D13394)
                     = ;                     &nb= sp;              Index Cond: (ca_address= _sk =3D ws1.ws_ship_addr_sk)
                     = ;                     &nb= sp;              Filter: (ca_state =3D '= TX'::bpchar)
                     = ;                     &nb= sp;              Rows Removed by Filter:= 1
                     = ;                  ->  = ;Hash  (cost=3D185612.75..185612.75 rows=3D37053 width=3D8) (actual ti= me=3D898.519..898.521 rows=3D42249 loops=3D1)
                     = ;                     &nb= sp;  Buckets: 65536  Batches: 1  Memory Usage: 2163kB
                     = ;                     &nb= sp;  ->  HashAggregate  (cost=3D185242.22..185612.75 rows= =3D37053 width=3D8) (actual time=3D888.235..893.423 rows=3D42249 loops=3D1)=
                     = ;                     &nb= sp;        Group Key: web_returns.wr_order_number
                     = ;                     &nb= sp;        Batches: 1  Memory Usage: 3345kB
                     = ;                     &nb= sp;        ->  Hash Join  (cost=3D2942.67.= .163474.00 rows=3D8707289 width=3D8) (actual time=3D223.693..825.681 rows= =3D518567 loops=3D1)
                     = ;                     &nb= sp;              Hash Cond: (ws_wh_1.ws_= order_number =3D web_returns.wr_order_number)
                     = ;                     &nb= sp;              ->  CTE Scan on= ws_wh ws_wh_1  (cost=3D0.00..141904.96 rows=3D7095248 width=3D4) (act= ual time=3D203.411..706.045 rows=3D719205 loops=3D1)
                     = ;                     &nb= sp;              ->  Hash  = (cost=3D2045.63..2045.63 rows=3D71763 width=3D4) (actual time=3D19.983..19.= 983 rows=3D71763 loops=3D1)
                     = ;                     &nb= sp;                    Bu= ckets: 131072  Batches: 1  Memory Usage: 3547kB
                     = ;                     &nb= sp;                    -&= gt;  Seq Scan on web_returns  (cost=3D0.00..2045.63 rows=3D71763 = width=3D4) (actual time=3D0.026..12.516 rows=3D71763 loops=3D1)
                     = ;            ->  Materialize  (c= ost=3D0.00..2.40 rows=3D5 width=3D4) (actual time=3D0.000..0.000 rows=3D5 l= oops=3D896)
                     = ;                  ->  = ;Seq Scan on web_site  (cost=3D0.00..2.38 rows=3D5 width=3D4) (actual = time=3D0.046..0.052 rows=3D5 loops=3D1)
                     = ;                     &nb= sp;  Filter: (web_company_name =3D 'pri'::bpchar)
                     = ;                     &nb= sp;  Rows Removed by Filter: 25
                     = ;      ->  CTE Scan on ws_wh  (cost=3D0.00..141= 904.96 rows=3D7095248 width=3D4) (actual time=3D0.001..25.301 rows=3D358222= loops=3D121)
 Planning Time: 3.432 ms
 Execution Time: 6512.766 ms
(59 rows)


The difference between both query plans is the second one uses Materialize = instead of Memoize. From the code, it seems that changing the usage of the = cache brings performance improvement unexpectedly.


Environment:
For the benchmark, I used 1 GB data, and my entire data folder can be downl= oaded here: https://drive.google.com/file/d/1iK5gfyKudfn2BczpoZbNRY_IAD_rIT= Zu/view?usp=3Dsharing
The connection string is: postgresql://ubuntu:ubuntu(at)127(dot)0(dot)0(dot= )1:5432/tpcds"
tpch=3D# select version();
                     = ;                     &nb= sp;  version
---------------------------------------------------------------------------= -----------------------
 PostgreSQL 17.0 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.2.= 0-23ubuntu4) 13.2.0, 64-bit
(1 row)



Best regards,

Jinsheng Ba

 

Notice: This email is generated from the account of an NUS alumnus. Content= s, views, and opinions therein are solely those of the sender. --_000_SEZPR06MB64941B811E7F9EE81978367E8A2E2SEZPR06MB6494apcp_--