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 1tFgH1-00F0Fk-P7 for pgsql-performance@arkaria.postgresql.org; Mon, 25 Nov 2024 21:05:36 +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 1tFgH0-000irU-3x for pgsql-performance@arkaria.postgresql.org; Mon, 25 Nov 2024 21:05:34 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tFgEX-000e4l-OA for pgsql-performance@lists.postgresql.org; Mon, 25 Nov 2024 21:03:01 +0000 Received: from mail-tyzapc01on2138.outbound.protection.outlook.com ([40.107.117.138] helo=APC01-TYZ-obe.outbound.protection.outlook.com) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tFgEU-003pNS-Iv for pgsql-performance@lists.postgresql.org; Mon, 25 Nov 2024 21:03:01 +0000 ARC-Seal: i=1; a=rsa-sha256; s=arcselector10001; d=microsoft.com; cv=none; b=e+tLczrjBcuch6c2xgCF/pcesNGdS59PPB6B8vmC98G2sd137nCUNGchGpiDdFqpAYWD5udZm4+rpHDlgHxUbb5y06ftKtE+Cllt/7w4V7ZO5B83/Awn+24krvhQvb3X4lm1o7LUUCOtUDbpS5iweDXQcObrsHsf7jk8DMS5KLjuvr/SzbWLhQczgiJ6MNLykEQm8pPQNT55Uc16AzmoJr61liULqYr8FGdhYSHq9GeMsIssNvc50h8GdpzSUdV2VGILSS4CJ3tbSpQHKWgW68UUYJJYiYM+g2z4/14+8fUDDmvGlLoWR+ubKOO23Ht8/2vzQR0aWfcLOaSN+f/6jw== 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=FY0j6BbcJwWcJDdC3YCWKfGZnSb/l4yh52be/uPa+bI=; b=bnE8HZ+V3SxnFOz9+1kblY0lrPwkKkhxHwC6OzfeG7YeKDNdmASnTfaUSnzl1lOvjZpf4ocMr0Kmcbw4ghMOGTn9FNmkUhEoaEAeyIGuAmjx7xEGyZsodrZlNYFuRRtV453oNudNXhZ8T2KeLl62TzQqmMOHKbsfFyP5DIxsPeABvlkDB0Bb1lgx0a4CebXC1qrdTfLklutWoFq3kPmBOOoGD6GmpftS8qqBjn7cf34lbRaW7Auc3sMANB3h0x0Gw2YAMoepbdzucyPstslaxWbqeV+vtpAMq/Jfuiyn0Q+NHTUQMibhhdj4WTeeWK/NHaqFcWdvj3T9iyhoP/6tuQ== 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=FY0j6BbcJwWcJDdC3YCWKfGZnSb/l4yh52be/uPa+bI=; b=dWl1ljM+6WLAPGd5hE2tsa3Cg4FzEcChVuShXTvJYgcwW0DvH7pHz3gwE7M6sk8Q7dhv1lP6LEDAUkGOSFLqZ+tP7Na/Ohd+CUcqQS3uIcwidd9tD+D9FKOWSl9AFZu1H2Q3bzAq4qThQM4p/RKBissUKe5M5EcxtbUmJC2IELmF1agG23FgvcOG6GEOK7g8zEDxbJH/03zhJgSZwfjF1Rmds7gjA2CWikcY6tw7TtPfECLQEQDXXaHNpyhJymREP0EcGhUUeRIKSi4mfvcsIroKKFsGq7nJJ1tqQb80O/YD59CTFWB260iSqDjbSZpaAXIR6J6SmexK+hV2MgqyMQ== Received: from SEZPR06MB6494.apcprd06.prod.outlook.com (2603:1096:101:18b::13) by OS8PR06MB7301.apcprd06.prod.outlook.com (2603:1096:604:28a::6) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.8182.18; Mon, 25 Nov 2024 21:02:46 +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 21:02:46 +0000 From: Ba Jinsheng To: Andrei Lepikhov , "pgsql-performance@lists.postgresql.org" Subject: CTE Inline On TPC-DS Query 95 Thread-Topic: CTE Inline On TPC-DS Query 95 Thread-Index: AQHbP3xAl2uxo88ahUyo8i2nu2nt5w== Date: Mon, 25 Nov 2024 21:02:46 +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_|OS8PR06MB7301:EE_ x-ms-office365-filtering-correlation-id: dcd51070-4a33-42d1-0e83-08dd0d948326 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?ZERgcBwPxEiG2oUAa1XGWj+Tj3vkez/YY6VBVtxxVEdudM0Q8fs3/t07Y8?= =?iso-8859-1?Q?DPbrvOfIYWH2T9SBbuq8e75ITO2DHrd6pV383Lb7m9c8aFBSt2WcHadfIc?= =?iso-8859-1?Q?doBm3G8w1VCrssNJQeHlC9pQITtT6wLDFE6tCW5+jDviCfbzXY8fmlDEqD?= =?iso-8859-1?Q?wIFl+Dm8C7MfDhI/9myrPNxh2tZSoqcKRkhtxp1/i6fWaDscyLXAN4YeTB?= =?iso-8859-1?Q?TE5rHXTUIXxWxcZaHCFmARA740feUznkLWHn7o4Gq4PQDzpvDKfsbFc9yZ?= =?iso-8859-1?Q?L0IlRpAWVJ3UijWYg6h+u4Xez4GpNFiQ8r1D1tWZujE9CyP9YasG0/8MXt?= =?iso-8859-1?Q?DOEq1XDGNktaYPhFMbKxff7WVMLtfsTXY2aXIzYxxdpsK85aqVEZrJbK+i?= =?iso-8859-1?Q?xQ8v9rX0UIT3zETFPYNKvEbP21ftUOyrLgm23pp7Rb+rmgFzEE3TbWGTcD?= =?iso-8859-1?Q?4Ca7mRUziKMxsQUJe40MoeWG72tNAkVwtvTdEb/PW2dbeKDJy/4iIF+QT3?= =?iso-8859-1?Q?bMwjgGVcj9Lg3+2ETFT6ZZJ2TuNlAEhFYr6e5SmBZ+Ne4jeRvY6V4R+OAx?= =?iso-8859-1?Q?FBdc0It2nWSKzBHzwxO4BhiZamOS2btc3lvz1ddWzKAZeCmgS/QuduNPZO?= =?iso-8859-1?Q?Ch1KrWNV6nc80UzDwwd7Qy8IRCcykGTk8jpGTFXZQv7T0nUSCC0pjmz98B?= =?iso-8859-1?Q?41j7gIVRK/xwD4R7WfamPDDKs4IFeDxdDmXS0/Q/JDIhHSla6McA+q67GK?= =?iso-8859-1?Q?bbpiQLpj3cs5CG3KF9uy3QzkpAQWQDlob0XvWuQ5PJEoq024b9OC6Cd+4U?= =?iso-8859-1?Q?QXUURMcQ5nw4wdsuJ2IXZWb06MM+AQRucr/Dn4HY9VCnKM7zAqyG3XP9H9?= =?iso-8859-1?Q?jykNvz/PXlmhBRUozwwbRdjo2uoQ2eJPaaHcXkMgsUJbgpYL16R2Pd5GTt?= =?iso-8859-1?Q?8ykITfjbqYDDhbAf2JEWgYb2/eNM5IQI0iHk1bzt/LygK3fdDriTVNDyT+?= =?iso-8859-1?Q?e+2owWNFr4uWGkOgO8ukBGl6x7N+Pd5eP2DD7lHiH/wMuMpo3lazgyDq88?= =?iso-8859-1?Q?Ug2dFmcpystSZA2WEZ/In7JyrdXpcn++xyy4Q8fQrUBNWwUmnmW7XYH0E1?= =?iso-8859-1?Q?1+Isz4eBwYVpv3yAqfUlCnDXKccBFLwGk7AOudgFdpbDKInq4HYFfILCj0?= =?iso-8859-1?Q?OTGIvDrgCDjibzDbcMZYlPMXhotsPbyM2mwurBfnCeWR2ABv7VbEOFXTCv?= =?iso-8859-1?Q?Wi6RCFInhTezBfgCiLDOkWrw4CpP+roXEUWo3l/pC3AmGRoYLCkc5/MC5s?= =?iso-8859-1?Q?iNmfabT5477Ghb8RatUe6WlIXycLo3DEPLlI91fO8UmOqjF4zFCpZsV4md?= =?iso-8859-1?Q?VlJzyt0e4F5/hRQvlttYW/oeBkmx45EnnwqA6m0A1cOmBGzR3e/C2vVR8u?= =?iso-8859-1?Q?TBdtDEj+GH1f5ma3KTRll7Sp6Oqs+eG8wYnqcg=3D=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?vRPaEPkUubzZLdLi8uWQLnVUMMvG44VJgjkf9AWnXNe7+WIjeUaMxiELsF?= =?iso-8859-1?Q?2oGcNkbdUectH+hynA31ObdUzqTY2eKa4tTpUhwuzcqeWk+xEks74098jz?= =?iso-8859-1?Q?F/d/dFA37wyiX8ASGf1owm+B5Ce/jZXOnwgeUIuKQPKRx10W4pJlr4Whxg?= =?iso-8859-1?Q?NwTpXWrl6aIVkItroIEQMhqZxqBNJi5w2YvNxyIpY/UA8bgq9A42Sd1en+?= =?iso-8859-1?Q?VtD5X5NKy1cRlCSBe2URHdqLpTX/fjgGWvZxtvD6Q7GDn5/QAN1U30M4La?= =?iso-8859-1?Q?mwZYf3MJn+L+XxSFinmCDOnRM68HnPqHBOXDZq8p16acrIROcxGsTBSgUb?= =?iso-8859-1?Q?nolprezLx+CEIMfj4vUVrVG0PE6b2GPUxOVShfjysCMzs2z4EH9jIg5Wfm?= =?iso-8859-1?Q?dDlydKSDt44s6qG55UIxeXa3ftnnLSn0OZ/Aiu3q9l8Hrm6RMrmMi69dZA?= =?iso-8859-1?Q?0362JEj6lSl/Icjb0DeyfVBQpJoF6yullOLzfj7/QLexn7yhi2oKTa8hzB?= =?iso-8859-1?Q?ygDiHlsMxj7sGkIB15lWGlGhRVfTGdNNabXa6a8VDIGQiyePZqoZbVZw/k?= =?iso-8859-1?Q?onIqaHAC/KbPjlDGu2/YWbUK6IwzOq4Lu/v32oEkuLEBmWHiibSqXKciiw?= =?iso-8859-1?Q?HX2ZutMeLfzpowogA6z0LtKlgZ/gJhHvd7GY/GrASTx5DjCbPgoMcfDazZ?= =?iso-8859-1?Q?y0+HackrVU/7TXbex08pFgGiflRAaz5sYd0704azAP7+hgHYaHw3E8C3c+?= =?iso-8859-1?Q?HJFr9V8p5TJfycYlHIRytsvkDLTyjRLsQiOsDd8r5i6dTb3tlkT3EAOC+h?= =?iso-8859-1?Q?MwZr5sT3EJqCHIZIMN4JyBd9zgqBFM5P6ehRJIrMBmKoBkN0ew1S3njlH2?= =?iso-8859-1?Q?3Kw/vPq/WvvN29Z0Olm2Cs2DbUZxywXwxRAv9sSQpicVmgsJudERM6s/0x?= =?iso-8859-1?Q?7uDW91g0tr3mOBrAyNB/YU8CJBPANO51nwgSe5VSNjZEtKaiSmS/qfJGWf?= =?iso-8859-1?Q?NvxvLz6bkVYtDfonRMU6aJnAFXpKVbnnz/eQErFsf+KJ2kNC9TbaE8fvkL?= =?iso-8859-1?Q?VbgY4HBe/tpyuaiBamG3sJT+D8feJib8IuEgOVKw3ThHPwQ6XlrcyPPKhQ?= =?iso-8859-1?Q?t2pi0Ow6orUcHARL+hvziyPuhTVkhbbRF3cqMEPLk5USmQ02n5Rzcgh+F4?= =?iso-8859-1?Q?trg6yvd60q0MIhkoMacvANPAN/nX80ZE7e5milFt0qsgkNacHq2Jsd4qhj?= =?iso-8859-1?Q?jVuhUq6B+W9MGsC0fSKFeKFMWROQFqKrvn+Uqby5QKPDBUNQE6+yDONJfV?= =?iso-8859-1?Q?NGygzXO5OHuJvjpe2O+BFktZxxCMmR7sYIlAhpOCRTQO/cc8AOK/nF+d2y?= =?iso-8859-1?Q?YvQMX+en7f1Eptg7qvyozeQ9dEN/ge27coTm9el+9ZEg5ABn+14liQW2n7?= =?iso-8859-1?Q?eoIXQ4j6ceTkeYq4PCOQUlPQUjyvJkZtOeVSGKCHdj2GceEsvTyBv5XUCq?= =?iso-8859-1?Q?VeexA2iBJbKnXkR8MVVc96LEMYGOARIgeVorGx1WOwaLJiPfCm9oOL5nGD?= =?iso-8859-1?Q?nP9GEEglJwxVf0++GzRUV+hD1PamhoeHXQhcJv2lvn0DEzociNCctpDvwV?= =?iso-8859-1?Q?Bo+5uZ6pNdi94=3D?= Content-Type: multipart/alternative; boundary="_000_SEZPR06MB649446E1B2745EEF5B49BA238A2E2SEZPR06MB6494apcp_" 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: dcd51070-4a33-42d1-0e83-08dd0d948326 X-MS-Exchange-CrossTenant-originalarrivaltime: 25 Nov 2024 21:02:46.3035 (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: f4y5Vog7XZSKxJIs8W73jAyq9GjFYgrSgxc42XKtgiPTklh9Pkhuizoezr3okTFuyORo+gBV/I6P67oyMTB2xQ== X-MS-Exchange-Transport-CrossTenantHeadersStamped: OS8PR06MB7301 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_SEZPR06MB649446E1B2745EEF5B49BA238A2E2SEZPR06MB6494apcp_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi all, Still for the 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; If we specify ``NOT MATERIALIZED`` for the query, the execution time is red= uced from 1min to 1s due to the usage of inline CTE. It seems expected as d= escribed in the PostgreSQL documentation. However, from the code: https://g= ithub.com/postgres/postgres/blob/REL_17_STABLE/src/backend/optimizer/plan/s= ubselect.c#L939, I understand that this query does not contain volatile fun= ctions and includes simple predicates. I am wondering whether we can relax = this condition checking, to enable inline CTE for such queries? Because it = seems the performance benefit is huge on a standard benchmark. 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_SEZPR06MB649446E1B2745EEF5B49BA238A2E2SEZPR06MB6494apcp_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi all,

Still for the 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;


If we specify ``NOT MATERIALIZED`` for the query, the execution time is red= uced from 1min to 1s due to the usage of inline CTE. It seems expected as d= escribed in the PostgreSQL documentation. However, from the code: https://github.com/postgres/postgres/blob/REL_17_STABLE/src/backend/optimiz= er/plan/subselect.c#L939, I understand that this query does not contain= volatile functions and includes simple predicates. I am wondering whether = we can relax this condition checking, to enable inline CTE for such queries? Because it seems the performance be= nefit is huge on a standard benchmark.



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_SEZPR06MB649446E1B2745EEF5B49BA238A2E2SEZPR06MB6494apcp_--