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 1tADjK-00EQ32-1p for pgsql-performance@arkaria.postgresql.org; Sun, 10 Nov 2024 19:36:18 +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 1tADjH-008SPi-2z for pgsql-performance@arkaria.postgresql.org; Sun, 10 Nov 2024 19:36:11 +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 1tADjG-008SPY-11 for pgsql-performance@lists.postgresql.org; Sun, 10 Nov 2024 19:36:11 +0000 Received: from mail-eastasiaazon11021096.outbound.protection.outlook.com ([52.101.129.96] helo=HK3PR03CU002.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 1tADj8-001Ezv-F1 for pgsql-performance@lists.postgresql.org; Sun, 10 Nov 2024 19:36:09 +0000 ARC-Seal: i=1; a=rsa-sha256; s=arcselector10001; d=microsoft.com; cv=none; b=PznDy4qoYi63YeofnSmVluQwMBCdOsDzPayYTdWF+EfQX9pbz5RMaUEjQPH8gVP8qkd7LwDNUFPbc2lnD5kVYunUmaR9p/E1t/XCDh/KuI+5SgEz1KrXWcFssRlzo5UuE8mFa6mivsV/AQYPUws2t9pBN2ETuxQc9s2Grb6r9s6UZA4NJlw4VxXr/f6q46RKo0MiDeF5vrNs9ohWpcoWQ4XCLw4XC5PfnEWqskBw7zt1Gf5TMrMhT4igyUSUZsLkf8PbYn2aQKAoHgVtaNjwhgoqcg0yN6soCVlBG03A8p44CM8dZTjwcQYFTs61xl/ARy6ApDU3whDUoloKsrs1tw== 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=HeRVIrO9/hiwBDzOF4Lyo40J6NWOqSIM+vKYLlZoB9U=; b=o7tSMDRLieJR68JR9Dxmx47wopfk3I0mIPWydteOI/2uVt/eiQucTDz+PbVJpKl4LJEDWVN1ZlnA71R0prOV4VWEg0ANzv7NSrZjkbB28MACAGGbudqJcf2DOSXoKXF5CsJj5mMJypBb2O5taFkuDn4R4qRI+AaTagMupMcPwP+aPOpHiuyLrtb57z+OalhgNY34Vn+x/mQnkH6yvxYdfWcn4K/x/g3kOstK/XG/0aoFjH+ZCs2Vi2GNuColQfG2b3c+oQy7XYzN2yIDa5vMB+iyMY5uhvupq99bwVF2e4r5AQd+D2nsMu+xSatcQFLifXrx1RSwhD2cviQljLFAZQ== 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=HeRVIrO9/hiwBDzOF4Lyo40J6NWOqSIM+vKYLlZoB9U=; b=lhz+DbaJtVW4YoSALA3tZl8JSb9ujB6+ITCTojsmePiShPNGdTnnfWTAZPyA9L0+gnaAtHIMd/eXoCPuG13RNdbgtyaVsUO0+lmvJXTWwmEWGAtqe5lZHToLb8sf1b/xVqLsMUBsDUZP1PEXZykKRTzxp6lYeuh7tn94ivlCVhWexo6xuED/vdVLF/znlLpK/o2z9aUpWZNyd2aeoU2p73jKN4/eFoxbHDHYKKUp+aqlU5qLY23SNfqENMKSiGH8DiWvlWi4DUycNtaj2GmXljHoOXC7RMQT929bB3PPS93Qal4XiUxrXKjSAh2UPFgA5LvBfTzm5B/Z+YTD9qcefg== Received: from SEZPR06MB6494.apcprd06.prod.outlook.com (2603:1096:101:18b::13) by SEYPR06MB5671.apcprd06.prod.outlook.com (2603:1096:101:bd::14) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.8137.17; Sun, 10 Nov 2024 19:35: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%3]) with mapi id 15.20.8158.013; Sun, 10 Nov 2024 19:35:36 +0000 From: Ba Jinsheng To: "pgsql-performance@lists.postgresql.org" , "lepihov@gmail.com" Subject: Performance of Query 4 on TPC-DS Benchmark Thread-Topic: Performance of Query 4 on TPC-DS Benchmark Thread-Index: AQHbM53XJabm3wvFF0icxJuGGZYSBw== Date: Sun, 10 Nov 2024 19:35: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_|SEYPR06MB5671:EE_ x-ms-office365-filtering-correlation-id: c6bc72b5-85fa-4234-e87e-08dd01bed979 x-ms-exchange-senderadcheck: 1 x-ms-exchange-antispam-relay: 0 x-microsoft-antispam: BCL:0;ARA:13230040|376014|366016|1800799024|8096899003|38070700018; x-microsoft-antispam-message-info: =?iso-8859-1?Q?m3G6w4VFF0r8tnaJia9cDC3ZwbmFjtLYdt/n6yYI8SkkSUGy/9LnDLY6OP?= =?iso-8859-1?Q?OsH6ISF6T3VepT7/6pYnou3oFvtgyxoJEkAiKheJFaDRdDg1awGfuzc1bi?= =?iso-8859-1?Q?AXEEwEjnJseQrVlRiZhq35tmSg24vMTCjL507CHKRejKxmQ8JDWv5/MaVH?= =?iso-8859-1?Q?g64CIqX6okbu3pLTNq1HPaGdtmNsoqpN6Iq0F20fz2Meo+vfNU6TQHXIot?= =?iso-8859-1?Q?l9v/Ue/J2E0rnWP/C1arhI+tT0p/Hwy3iza2y9IGjaT4cbbpmmoCo6VAWP?= =?iso-8859-1?Q?qr+UvIGlvz1WNq5fAAhjAWBoOjJigO7oRzE17Rk9bqd3bNkL8kiseDkqu7?= =?iso-8859-1?Q?kYgtTifIb2MZbLn6eHbSazJeHB4/UM/2c2l2NXsn5SoT8SK3u6/UkzF4Pe?= =?iso-8859-1?Q?RzPJfxVYhgEmAd7WxUXd4jazcbGMCZEozPOcDnmnh5bAMg0UcYqirG5vyw?= =?iso-8859-1?Q?DAOINrzB4pVdVLT4dnXlZpd/ulveQ+ZVs9Iajxp0dgSwKCP4oZD4qFGCui?= =?iso-8859-1?Q?iO7uWLe0bBbjE9fCZwDW+xiroAqqSdfiIWHq21D0Ybx5I9O3W1paXycJNk?= =?iso-8859-1?Q?NuApaDZeP5HBSn75NZCWHm7rgdrd/ArAn/c6pxGPSzN8b+smbOOo/buvGS?= =?iso-8859-1?Q?p4rgfiW5CET+eL2odd/0M1I9/ooA+H8b///0iXKSovL9XnpnAZ8mC3QZVy?= =?iso-8859-1?Q?LB3o+f6+IcytpDDMvWSyNhSAc2j60eoN7AhJQpoB9s2MLM8DmScAGTP2qH?= =?iso-8859-1?Q?DdzwEyErrVsnocmGGkNaHn6p6Z7083+C+iBoIRDrwBeuxjDAypAbOn7cug?= =?iso-8859-1?Q?1rbHh8YpPU7ucDIfNt6oKNxdbgYXgIjVvzWTjIKzwuxDwHRjyPHRMCj3mn?= =?iso-8859-1?Q?5jGcZ+IcTbi7BvAeV3X7JfUMpsnCXrFHv+zo6QYuflTI0alS9eoeTBa6em?= =?iso-8859-1?Q?dP4+h+PXaKXfeOCY8COj/ap1p6M25ChQtoX0rl9+L/JLKxz7uZNBT7+40s?= =?iso-8859-1?Q?EgOAWm6R8jG6kmMySa3iB9trsI4K/FwVIqcxFNjBfvn2HaPH8ePrWX8Qc4?= =?iso-8859-1?Q?ozDyxUHDjHQmgsI5JWQYxtcA0VpP1CTpHO/r1/LwsE2jQ5KwY+r436ZbNU?= =?iso-8859-1?Q?5v8MLeTjbEuumxhEU4UwLPDhZU5xHNKIp5UzA5GBl9FX4ZKCNBjU6F30Di?= =?iso-8859-1?Q?qgxCND8/zIILzfbUVlgW8o37Kd16vy5jThqmavvGgR8o3gXDS+7cQFvGQo?= =?iso-8859-1?Q?QE1reEipfr9aBIZtkkZ1ZAs+10kxA1kOcRQx1RsjEZ8bZzWBNSa8ljh3Fs?= =?iso-8859-1?Q?ycMAgYxx6orvis981R5FEmdul8luXXET7pLVNllx3HT9xM4tYsDjGleZFo?= =?iso-8859-1?Q?OTxkmVSr+e?= 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)(366016)(1800799024)(8096899003)(38070700018);DIR:OUT;SFP:1102; x-ms-exchange-antispam-messagedata-chunkcount: 1 x-ms-exchange-antispam-messagedata-0: =?iso-8859-1?Q?Rgxk+xMQfgszZ0JAjuuk2R2NHhDGfXhFLa+gIGHa5J5/6Z2X4CiunSKeAM?= =?iso-8859-1?Q?3JYk/QCF6mMXdDawfWXL9PbAKrykHmg3DTV+Mn1nhAUpfKxNm3iI1vn+xx?= =?iso-8859-1?Q?E0yHW5laysnaQ5DMstTQBF9aMtNOGUsKas9X75J2Qb5G99UZ31VerGr522?= =?iso-8859-1?Q?zjmEGBfe9RsdxMIYwkGTrxCicjm2kcNyhL+nPEPIt1hkFlrdBTcVTG0L/x?= =?iso-8859-1?Q?LgGhf7swbpv9bDPzMHYJj5/dDjRieBLCM/HW8YZGJJ53a9zBPQxCHsGfW3?= =?iso-8859-1?Q?0yzXggbTn1nO5I/f1/XwT7Irw4xAi8tIzeyTWTP+07P/+XVgyI7nNz2oFE?= =?iso-8859-1?Q?LsOGUhSGqNBg5sTolFmyJBDa0Wr1yWv1OyphJrlytDJ9MwiwN+nlLWwLKy?= =?iso-8859-1?Q?qTZxOJ9L6ifjCdBHUxX0xH0vTwusFKAg9kbFK3zjclfBoE1TiPqrPKIJ87?= =?iso-8859-1?Q?1okVZ7D2S8AX81e7fMaMSu2Zn4Kkg8LGIYsPaPHz6SObF8lzXTtOyB9s6K?= =?iso-8859-1?Q?gQDjHM09qGlkFUe4p7V1at2vLXE2nNCNWRLPrV22qJpwNgKoyw2/MP7z99?= =?iso-8859-1?Q?wW42szlunAyiZQtxkT2Uh23K9kr4Z9Cbi1qG/Yjtz1ee60shV3N4ZYblhS?= =?iso-8859-1?Q?CFp29ufOhx+gcceQEePKgLNKXfrHCnr3xqBaz9LgVCsoCQ6n9qDqhm81bk?= =?iso-8859-1?Q?vG5g6Ts1V8pzta27i5ppDvGuI+5m1thMuikCKor29vMVm6on9pjeE3sgWr?= =?iso-8859-1?Q?NW2WMg07MpQ5pqdn19+tFZA1lqY2HHaowgeZmTeiI271ZxqpvcAimWtEey?= =?iso-8859-1?Q?g58sGaC6e1Lv4jZtVzwU+9CG/6S/LtxmvHIghRFozIEMSSv9GWTWe2QIl1?= =?iso-8859-1?Q?c29na5Aal38eGHCjtapUp/0yQstHl+HC+s+jfZ3wPvkC15TwiCiRnqbYU+?= =?iso-8859-1?Q?D29MwWeuA0UHYb1QvvEZs7se2xRCppyPCz8LdTK5TwibbN+RixLPdsUpg8?= =?iso-8859-1?Q?3XGDg9JXrJ9s6I5sDTx3la4H6VmYJ6HoJ2gD0hD9gqF25NAFsIWJbk524f?= =?iso-8859-1?Q?aodEWN+cda/HvIzoNwyEwasgw11f2Cy2B+l1f1/wFb6puj2haE9VqWNjjM?= =?iso-8859-1?Q?iHEPvZtSXDPZWW/vBJ3XL5qZ9F4O7h+itLTqWRofcyWOlSlaTC0I1h1bQZ?= =?iso-8859-1?Q?i8xHIWtEsBBQyw4LvO1uYUWRgSad91+kv4EqjYjF84059w2ORtJLvaG8Sp?= =?iso-8859-1?Q?X+y7jFLLEYilIvayeS7THLkosvEdwFKlA22vDByEz3HO9fWwiA3UhMUCzq?= =?iso-8859-1?Q?lpL3WTXePqqhNLYLix5gRVw7tpw/imFrrpKYtsCDqz6Wv3f1j95zRRjJ1T?= =?iso-8859-1?Q?Ufdoae9hlABuAJl9kNlZkiq3PXlFbWpZhk/z3+/jnjSALReFmU0EhNQ1pn?= =?iso-8859-1?Q?CfapFeAKlZobE6Y5eEiYaq7jJlVpkPSgXyfaWExAGryYXUuaEbQFdUsEDK?= =?iso-8859-1?Q?zCVWPdndNaOSbJ3wA/x4mSIdZjm15wPdiaaZSzffeCY0Oudpccz2eXdyWA?= =?iso-8859-1?Q?YeEq4rv9+lujTYXzEveLKRw0vYivMQTJpHXDme0NkKTYiLltYy0gQ76u8E?= =?iso-8859-1?Q?BJIw7TtK+JJgQ=3D?= Content-Type: multipart/alternative; boundary="_000_SEZPR06MB6494F6A2837995BDD4E0BF9A8A5F2SEZPR06MB6494apcp_" 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: c6bc72b5-85fa-4234-e87e-08dd01bed979 X-MS-Exchange-CrossTenant-originalarrivaltime: 10 Nov 2024 19:35:36.0348 (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: h0rell5xqVxvTCylI96wR2BvTGuTHJHnGQXqY3kLscNDA6A6tk6Z30HPGLK77W0opjY5GiMMQpL5do4Bq1jp1g== X-MS-Exchange-Transport-CrossTenantHeadersStamped: SEYPR06MB5671 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_SEZPR06MB6494F6A2837995BDD4E0BF9A8A5F2SEZPR06MB6494apcp_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi all, Please see this case: Query 4 on TPC-DS benchmark: with year_total as ( select c_customer_id customer_id ,c_first_name customer_first_name ,c_last_name customer_last_name ,c_preferred_cust_flag customer_preferred_cust_flag ,c_birth_country customer_birth_country ,c_login customer_login ,c_email_address customer_email_address ,d_year dyear ,sum(((ss_ext_list_price-ss_ext_wholesale_cost-ss_ext_discount_amt)+= ss_ext_sales_price)/2) year_total ,'s' sale_type from customer ,store_sales ,date_dim where c_customer_sk =3D ss_customer_sk and ss_sold_date_sk =3D d_date_sk group by c_customer_id ,c_first_name ,c_last_name ,c_preferred_cust_flag ,c_birth_country ,c_login ,c_email_address ,d_year union all select c_customer_id customer_id ,c_first_name customer_first_name ,c_last_name customer_last_name ,c_preferred_cust_flag customer_preferred_cust_flag ,c_birth_country customer_birth_country ,c_login customer_login ,c_email_address customer_email_address ,d_year dyear ,sum((((cs_ext_list_price-cs_ext_wholesale_cost-cs_ext_discount_amt)= +cs_ext_sales_price)/2) ) year_total ,'c' sale_type from customer ,catalog_sales ,date_dim where c_customer_sk =3D cs_bill_customer_sk and cs_sold_date_sk =3D d_date_sk group by c_customer_id ,c_first_name ,c_last_name ,c_preferred_cust_flag ,c_birth_country ,c_login ,c_email_address ,d_year union all select c_customer_id customer_id ,c_first_name customer_first_name ,c_last_name customer_last_name ,c_preferred_cust_flag customer_preferred_cust_flag ,c_birth_country customer_birth_country ,c_login customer_login ,c_email_address customer_email_address ,d_year dyear ,sum((((ws_ext_list_price-ws_ext_wholesale_cost-ws_ext_discount_amt)= +ws_ext_sales_price)/2) ) year_total ,'w' sale_type from customer ,web_sales ,date_dim where c_customer_sk =3D ws_bill_customer_sk and ws_sold_date_sk =3D d_date_sk group by c_customer_id ,c_first_name ,c_last_name ,c_preferred_cust_flag ,c_birth_country ,c_login ,c_email_address ,d_year ) select t_s_secyear.customer_id ,t_s_secyear.customer_first_name ,t_s_secyear.customer_last_name ,t_s_secyear.customer_email_address from year_total t_s_firstyear ,year_total t_s_secyear ,year_total t_c_firstyear ,year_total t_c_secyear ,year_total t_w_firstyear ,year_total t_w_secyear where t_s_secyear.customer_id =3D t_s_firstyear.customer_id and t_s_firstyear.customer_id =3D t_c_secyear.customer_id and t_s_firstyear.customer_id =3D t_c_firstyear.customer_id and t_s_firstyear.customer_id =3D t_w_firstyear.customer_id and t_s_firstyear.customer_id =3D t_w_secyear.customer_id and t_s_firstyear.sale_type =3D 's' and t_c_firstyear.sale_type =3D 'c' and t_w_firstyear.sale_type =3D 'w' and t_s_secyear.sale_type =3D 's' and t_c_secyear.sale_type =3D 'c' and t_w_secyear.sale_type =3D 'w' and t_s_firstyear.dyear =3D 2001 and t_s_secyear.dyear =3D 2001+1 and t_c_firstyear.dyear =3D 2001 and t_c_secyear.dyear =3D 2001+1 and t_w_firstyear.dyear =3D 2001 and t_w_secyear.dyear =3D 2001+1 and t_s_firstyear.year_total > 0 and t_c_firstyear.year_total > 0 and t_w_firstyear.year_total > 0 and case when t_c_firstyear.year_total > 0 then t_c_secyear.year_total /= t_c_firstyear.year_total else null end > case when t_s_firstyear.year_total > 0 then t_s_secyear.year_t= otal / t_s_firstyear.year_total else null end and case when t_c_firstyear.year_total > 0 then t_c_secyear.year_total /= t_c_firstyear.year_total else null end > case when t_w_firstyear.year_total > 0 then t_w_secyear.year_t= otal / t_w_firstyear.year_total else null end order by t_s_secyear.customer_id ,t_s_secyear.customer_first_name ,t_s_secyear.customer_last_name ,t_s_secyear.customer_email_address limit 100; The execution time is more than 50 minutes: = = QUERY PLAN ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------------------------------------------------------------ Limit (cost=3D1255378.56..1255378.57 rows=3D1 width=3D132) (actual time= =3D3024403.311..3024403.342 rows=3D8 loops=3D1) CTE year_total -> Append (cost=3D197433.23..461340.62 rows=3D5041142 width=3D216) (= actual time=3D4126.043..7897.747 rows=3D384208 loops=3D1) -> HashAggregate (cost=3D197433.23..233436.60 rows=3D2880270 w= idth=3D216) (actual time=3D4126.042..4231.703 rows=3D190581 loops=3D1) Group Key: customer.c_customer_id, customer.c_first_name, = customer.c_last_name, customer.c_preferred_cust_flag, customer.c_birth_coun= try, customer.c_login, customer.c_email_address, date_dim.d_year Batches: 1 Memory Usage: 213017kB -> Hash Join (cost=3D8151.60..103824.45 rows=3D2880270 w= idth=3D174) (actual time=3D69.110..1686.608 rows=3D2685453 loops=3D1) Hash Cond: (store_sales.ss_sold_date_sk =3D date_dim= .d_date_sk) -> Hash Join (cost=3D5103.00..93214.72 rows=3D2880= 270 width=3D174) (actual time=3D49.517..1162.567 rows=3D2750652 loops=3D1) Hash Cond: (store_sales.ss_customer_sk =3D cus= tomer.c_customer_sk) -> Seq Scan on store_sales (cost=3D0.00..805= 50.70 rows=3D2880270 width=3D30) (actual time=3D0.018..208.022 rows=3D28804= 04 loops=3D1) -> Hash (cost=3D3853.00..3853.00 rows=3D1000= 00 width=3D152) (actual time=3D49.271..49.271 rows=3D100000 loops=3D1) Buckets: 131072 Batches: 1 Memory Usag= e: 17161kB -> Seq Scan on customer (cost=3D0.00..= 3853.00 rows=3D100000 width=3D152) (actual time=3D0.011..26.448 rows=3D1000= 00 loops=3D1) -> Hash (cost=3D2135.49..2135.49 rows=3D73049 widt= h=3D8) (actual time=3D19.369..19.370 rows=3D73049 loops=3D1) Buckets: 131072 Batches: 1 Memory Usage: 387= 8kB -> Seq Scan on date_dim (cost=3D0.00..2135.4= 9 rows=3D73049 width=3D8) (actual time=3D0.037..11.763 rows=3D73049 loops= =3D1) -> HashAggregate (cost=3D114410.03..132428.63 rows=3D1441488 w= idth=3D216) (actual time=3D2369.202..2447.868 rows=3D136978 loops=3D1) Group Key: customer_1.c_customer_id, customer_1.c_first_na= me, customer_1.c_last_name, customer_1.c_preferred_cust_flag, customer_1.c_= birth_country, customer_1.c_login, customer_1.c_email_address, date_dim_1.d= _year Batches: 1 Memory Usage: 131097kB -> Hash Join (cost=3D8151.60..67561.67 rows=3D1441488 wi= dth=3D177) (actual time=3D62.483..974.143 rows=3D1430939 loops=3D1) Hash Cond: (catalog_sales.cs_sold_date_sk =3D date_d= im_1.d_date_sk) -> Hash Join (cost=3D5103.00..60728.94 rows=3D1441= 488 width=3D177) (actual time=3D46.571..687.972 rows=3D1434519 loops=3D1) Hash Cond: (catalog_sales.cs_bill_customer_sk = =3D customer_1.c_customer_sk) -> Seq Scan on catalog_sales (cost=3D0.00..5= 1841.88 rows=3D1441488 width=3D33) (actual time=3D0.029..128.238 rows=3D144= 1548 loops=3D1) -> Hash (cost=3D3853.00..3853.00 rows=3D1000= 00 width=3D152) (actual time=3D46.311..46.325 rows=3D100000 loops=3D1) Buckets: 131072 Batches: 1 Memory Usag= e: 17161kB -> Seq Scan on customer customer_1 (co= st=3D0.00..3853.00 rows=3D100000 width=3D152) (actual time=3D0.005..23.350 = rows=3D100000 loops=3D1) -> Hash (cost=3D2135.49..2135.49 rows=3D73049 widt= h=3D8) (actual time=3D15.677..15.677 rows=3D73049 loops=3D1) Buckets: 131072 Batches: 1 Memory Usage: 387= 8kB -> Seq Scan on date_dim date_dim_1 (cost=3D0= .00..2135.49 rows=3D73049 width=3D8) (actual time=3D0.015..7.957 rows=3D730= 49 loops=3D1) -> HashAggregate (cost=3D61277.38..70269.68 rows=3D719384 widt= h=3D216) (actual time=3D1166.953..1198.730 rows=3D56649 loops=3D1) Group Key: customer_2.c_customer_id, customer_2.c_first_na= me, customer_2.c_last_name, customer_2.c_preferred_cust_flag, customer_2.c_= birth_country, customer_2.c_login, customer_2.c_email_address, date_dim_2.d= _year Batches: 1 Memory Usage: 57369kB -> Hash Join (cost=3D8151.60..37897.40 rows=3D719384 wid= th=3D177) (actual time=3D68.327..508.594 rows=3D719119 loops=3D1) Hash Cond: (web_sales.ws_sold_date_sk =3D date_dim_2= .d_date_sk) -> Hash Join (cost=3D5103.00..32960.30 rows=3D7193= 84 width=3D177) (actual time=3D52.240..357.963 rows=3D719217 loops=3D1) Hash Cond: (web_sales.ws_bill_customer_sk =3D = customer_2.c_customer_sk) -> Seq Scan on web_sales (cost=3D0.00..25968= .84 rows=3D719384 width=3D33) (actual time=3D0.032..62.464 rows=3D719384 lo= ops=3D1) -> Hash (cost=3D3853.00..3853.00 rows=3D1000= 00 width=3D152) (actual time=3D51.959..51.960 rows=3D100000 loops=3D1) Buckets: 131072 Batches: 1 Memory Usag= e: 17161kB -> Seq Scan on customer customer_2 (co= st=3D0.00..3853.00 rows=3D100000 width=3D152) (actual time=3D0.004..25.350 = rows=3D100000 loops=3D1) -> Hash (cost=3D2135.49..2135.49 rows=3D73049 widt= h=3D8) (actual time=3D15.831..15.834 rows=3D73049 loops=3D1) Buckets: 131072 Batches: 1 Memory Usage: 387= 8kB -> Seq Scan on date_dim date_dim_2 (cost=3D0= .00..2135.49 rows=3D73049 width=3D8) (actual time=3D0.014..8.100 rows=3D730= 49 loops=3D1) -> Sort (cost=3D794037.94..794037.95 rows=3D1 width=3D132) (actual tim= e=3D3024403.310..3024403.313 rows=3D8 loops=3D1) Sort Key: t_s_secyear.customer_id, t_s_secyear.customer_first_name= , t_s_secyear.customer_last_name, t_s_secyear.customer_email_address Sort Method: quicksort Memory: 26kB -> Nested Loop (cost=3D0.00..794037.93 rows=3D1 width=3D132) (ac= tual time=3D354851.431..3024403.218 rows=3D8 loops=3D1) Join Filter: ((t_s_secyear.customer_id =3D t_w_secyear.custo= mer_id) AND (CASE WHEN (t_c_firstyear.year_total > '0'::numeric) THEN (t_c_= secyear.year_total / t_c_firstyear.year_total) ELSE NULL::numeric END > CAS= E WHEN (t_w_firstyear.year_total > '0'::numeric) THEN (t_w_secyear.year_tot= al / t_w_firstyear.year_total) ELSE NULL::numeric END)) Rows Removed by Join Filter: 810136 -> Nested Loop (cost=3D0.00..668006.23 rows=3D1 width=3D30= 8) (actual time=3D33554.075..3021248.646 rows=3D72 loops=3D1) Join Filter: ((t_s_secyear.customer_id =3D t_c_secyear= .customer_id) AND (CASE WHEN (t_c_firstyear.year_total > '0'::numeric) THEN= (t_c_secyear.year_total / t_c_firstyear.year_total) ELSE NULL::numeric END= > CASE WHEN (t_s_firstyear.year_total > '0'::numeric) THEN (t_s_secyear.ye= ar_total / t_s_firstyear.year_total) ELSE NULL::numeric END)) Rows Removed by Join Filter: 11876277 -> Nested Loop (cost=3D0.00..541974.53 rows=3D1 widt= h=3D320) (actual time=3D14866.104..3001271.961 rows=3D437 loops=3D1) Join Filter: (t_s_firstyear.customer_id =3D t_s_= secyear.customer_id) Rows Removed by Join Filter: 44702488 -> Nested Loop (cost=3D0.00..415941.57 rows=3D= 2 width=3D156) (actual time=3D11739.944..2946020.749 rows=3D1171 loops=3D1) Join Filter: (t_s_firstyear.customer_id = =3D t_w_firstyear.customer_id) Rows Removed by Join Filter: 112695277 -> Nested Loop (cost=3D0.00..277302.08 r= ows=3D9 width=3D104) (actual time=3D8139.729..2351733.795 rows=3D9952 loops= =3D1) Join Filter: (t_s_firstyear.customer= _id =3D t_c_firstyear.customer_id) Rows Removed by Join Filter: 9978958= 70 -> CTE Scan on year_total t_s_first= year (cost=3D0.00..138631.41 rows=3D42 width=3D52) (actual time=3D4126.046= ..4234.598 rows=3D37923 loops=3D1) Filter: ((year_total > '0'::nu= meric) AND (sale_type =3D 's'::text) AND (dyear =3D 2001)) Rows Removed by Filter: 346285 -> CTE Scan on year_total t_c_first= year (cost=3D0.00..138631.41 rows=3D42 width=3D52) (actual time=3D28.926..= 60.356 rows=3D26314 loops=3D37923) Filter: ((year_total > '0'::nu= meric) AND (sale_type =3D 'c'::text) AND (dyear =3D 2001)) Rows Removed by Filter: 357894 -> CTE Scan on year_total t_w_firstyear = (cost=3D0.00..138631.41 rows=3D42 width=3D52) (actual time=3D49.572..59.057= rows=3D11324 loops=3D9952) Filter: ((year_total > '0'::numeric)= AND (sale_type =3D 'w'::text) AND (dyear =3D 2001)) Rows Removed by Filter: 372884 -> CTE Scan on year_total t_s_secyear (cost=3D= 0.00..126028.55 rows=3D126 width=3D164) (actual time=3D0.002..44.949 rows= =3D38175 loops=3D1171) Filter: ((sale_type =3D 's'::text) AND (dy= ear =3D 2002)) Rows Removed by Filter: 346033 -> CTE Scan on year_total t_c_secyear (cost=3D0.00..= 126028.55 rows=3D126 width=3D52) (actual time=3D21.023..44.097 rows=3D27177= loops=3D437) Filter: ((sale_type =3D 'c'::text) AND (dyear = =3D 2002)) Rows Removed by Filter: 357031 -> CTE Scan on year_total t_w_secyear (cost=3D0.00..126028= .55 rows=3D126 width=3D52) (actual time=3D36.137..43.090 rows=3D11252 loops= =3D72) Filter: ((sale_type =3D 'w'::text) AND (dyear =3D 2002= )) Rows Removed by Filter: 372956 Planning Time: 4.529 ms Execution Time: 3024486.695 ms (83 rows) If we disable this checking: diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/= util/pathnode.c index c42742d2c7..d47d0f0e59 100644 --- a/src/backend/optimizer/util/pathnode.c +++ b/src/backend/optimizer/util/pathnode.c @@ -555,16 +555,6 @@ add_path(RelOptInfo *parent_rel, Path *new_path) } break; case COSTS_BETTER2: - if (keyscmp !=3D PATHKEYS_B= ETTER1) - { - outercmp =3D bms_su= bset_compare(PATH_REQ_OUTER(new_path), - = PATH_REQ_OUTER(old_path)); - if ((outercmp =3D= =3D BMS_EQUAL || - outercmp = =3D=3D BMS_SUBSET2) && - new_path->r= ows >=3D old_path->rows && - new_path->p= arallel_safe <=3D old_path->parallel_safe) - accept_new = =3D false; /* old dominates new */ - } break; case COSTS_DIFFERENT: The execution time is reduced to around 8 seconds: = = = QUERY PLAN ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= --------------------------------------------- Limit (cost=3D1255392.10..1255392.10 rows=3D1 width=3D132) (actual time= =3D8068.870..8068.887 rows=3D8 loops=3D1) CTE year_total -> Append (cost=3D197441.91..461358.17 rows=3D5041411 width=3D216) (= actual time=3D4004.918..7547.884 rows=3D384208 loops=3D1) -> HashAggregate (cost=3D197441.91..233447.56 rows=3D2880452 w= idth=3D216) (actual time=3D4004.917..4112.220 rows=3D190581 loops=3D1) Group Key: customer.c_customer_id, customer.c_first_name, = customer.c_last_name, customer.c_preferred_cust_flag, customer.c_birth_coun= try, customer.c_login, customer.c_email_address, date_dim.d_year Batches: 1 Memory Usage: 213017kB -> Hash Join (cost=3D8151.60..103827.22 rows=3D2880452 w= idth=3D174) (actual time=3D73.291..1594.391 rows=3D2685453 loops=3D1) Hash Cond: (store_sales.ss_sold_date_sk =3D date_dim= .d_date_sk) -> Hash Join (cost=3D5103.00..93217.01 rows=3D2880= 452 width=3D174) (actual time=3D52.407..1100.642 rows=3D2750652 loops=3D1) Hash Cond: (store_sales.ss_customer_sk =3D cus= tomer.c_customer_sk) -> Seq Scan on store_sales (cost=3D0.00..805= 52.52 rows=3D2880452 width=3D30) (actual time=3D0.017..213.629 rows=3D28804= 04 loops=3D1) -> Hash (cost=3D3853.00..3853.00 rows=3D1000= 00 width=3D152) (actual time=3D52.083..52.083 rows=3D100000 loops=3D1) Buckets: 131072 Batches: 1 Memory Usag= e: 17161kB -> Seq Scan on customer (cost=3D0.00..= 3853.00 rows=3D100000 width=3D152) (actual time=3D0.013..28.104 rows=3D1000= 00 loops=3D1) -> Hash (cost=3D2135.49..2135.49 rows=3D73049 widt= h=3D8) (actual time=3D20.589..20.592 rows=3D73049 loops=3D1) Buckets: 131072 Batches: 1 Memory Usage: 387= 8kB -> Seq Scan on date_dim (cost=3D0.00..2135.4= 9 rows=3D73049 width=3D8) (actual time=3D0.036..12.464 rows=3D73049 loops= =3D1) -> HashAggregate (cost=3D114414.19..132433.87 rows=3D1441575 w= idth=3D216) (actual time=3D2186.304..2263.365 rows=3D136978 loops=3D1) Group Key: customer_1.c_customer_id, customer_1.c_first_na= me, customer_1.c_last_name, customer_1.c_preferred_cust_flag, customer_1.c_= birth_country, customer_1.c_login, customer_1.c_email_address, date_dim_1.d= _year Batches: 1 Memory Usage: 131097kB -> Hash Join (cost=3D8151.60..67563.00 rows=3D1441575 wi= dth=3D177) (actual time=3D63.765..880.282 rows=3D1430939 loops=3D1) Hash Cond: (catalog_sales.cs_sold_date_sk =3D date_d= im_1.d_date_sk) -> Hash Join (cost=3D5103.00..60730.04 rows=3D1441= 575 width=3D177) (actual time=3D47.735..619.585 rows=3D1434519 loops=3D1) Hash Cond: (catalog_sales.cs_bill_customer_sk = =3D customer_1.c_customer_sk) -> Seq Scan on catalog_sales (cost=3D0.00..5= 1842.75 rows=3D1441575 width=3D33) (actual time=3D0.033..121.738 rows=3D144= 1548 loops=3D1) -> Hash (cost=3D3853.00..3853.00 rows=3D1000= 00 width=3D152) (actual time=3D47.433..47.433 rows=3D100000 loops=3D1) Buckets: 131072 Batches: 1 Memory Usag= e: 17161kB -> Seq Scan on customer customer_1 (co= st=3D0.00..3853.00 rows=3D100000 width=3D152) (actual time=3D0.004..23.960 = rows=3D100000 loops=3D1) -> Hash (cost=3D2135.49..2135.49 rows=3D73049 widt= h=3D8) (actual time=3D15.755..15.756 rows=3D73049 loops=3D1) Buckets: 131072 Batches: 1 Memory Usage: 387= 8kB -> Seq Scan on date_dim date_dim_1 (cost=3D0= .00..2135.49 rows=3D73049 width=3D8) (actual time=3D0.017..8.030 rows=3D730= 49 loops=3D1) -> HashAggregate (cost=3D61277.38..70269.68 rows=3D719384 widt= h=3D216) (actual time=3D1121.896..1152.982 rows=3D56649 loops=3D1) Group Key: customer_2.c_customer_id, customer_2.c_first_na= me, customer_2.c_last_name, customer_2.c_preferred_cust_flag, customer_2.c_= birth_country, customer_2.c_login, customer_2.c_email_address, date_dim_2.d= _year Batches: 1 Memory Usage: 57369kB -> Hash Join (cost=3D8151.60..37897.40 rows=3D719384 wid= th=3D177) (actual time=3D63.239..470.700 rows=3D719119 loops=3D1) Hash Cond: (web_sales.ws_sold_date_sk =3D date_dim_2= .d_date_sk) -> Hash Join (cost=3D5103.00..32960.30 rows=3D7193= 84 width=3D177) (actual time=3D47.365..329.499 rows=3D719217 loops=3D1) Hash Cond: (web_sales.ws_bill_customer_sk =3D = customer_2.c_customer_sk) -> Seq Scan on web_sales (cost=3D0.00..25968= .84 rows=3D719384 width=3D33) (actual time=3D0.036..60.068 rows=3D719384 lo= ops=3D1) -> Hash (cost=3D3853.00..3853.00 rows=3D1000= 00 width=3D152) (actual time=3D47.076..47.078 rows=3D100000 loops=3D1) Buckets: 131072 Batches: 1 Memory Usag= e: 17161kB -> Seq Scan on customer customer_2 (co= st=3D0.00..3853.00 rows=3D100000 width=3D152) (actual time=3D0.005..23.909 = rows=3D100000 loops=3D1) -> Hash (cost=3D2135.49..2135.49 rows=3D73049 widt= h=3D8) (actual time=3D15.615..15.615 rows=3D73049 loops=3D1) Buckets: 131072 Batches: 1 Memory Usage: 387= 8kB -> Seq Scan on date_dim date_dim_2 (cost=3D0= .00..2135.49 rows=3D73049 width=3D8) (actual time=3D0.019..8.060 rows=3D730= 49 loops=3D1) -> Sort (cost=3D794033.93..794033.94 rows=3D1 width=3D132) (actual tim= e=3D8068.869..8068.872 rows=3D8 loops=3D1) Sort Key: t_s_secyear.customer_id, t_s_secyear.customer_first_name= , t_s_secyear.customer_last_name, t_s_secyear.customer_email_address Sort Method: quicksort Memory: 26kB -> Hash Join (cost=3D794032.91..794033.92 rows=3D1 width=3D132) = (actual time=3D8061.079..8068.861 rows=3D8 loops=3D1) Hash Cond: (t_s_secyear.customer_id =3D t_s_firstyear.custom= er_id) Join Filter: ((CASE WHEN (t_c_firstyear.year_total > '0'::nu= meric) THEN (t_c_secyear.year_total / t_c_firstyear.year_total) ELSE NULL::= numeric END > CASE WHEN (t_s_firstyear.year_total > '0'::numeric) THEN (t_s= _secyear.year_total / t_s_firstyear.year_total) ELSE NULL::numeric END) AND= (CASE WHEN (t_c_firstyear.year_total > '0'::numeric) THEN (t_c_secyear.yea= r_total / t_c_firstyear.year_total) ELSE NULL::numeric END > CASE WHEN (t_w= _firstyear.year_total > '0'::numeric) THEN (t_w_secyear.year_total / t_w_fi= rstyear.year_total) ELSE NULL::numeric END)) Rows Removed by Join Filter: 11 -> Merge Join (cost=3D390716.09..390717.01 rows=3D16 width= =3D268) (actual time=3D7877.494..7885.971 rows=3D2793 loops=3D1) Merge Cond: (t_s_secyear.customer_id =3D t_c_secyear.c= ustomer_id) -> Sort (cost=3D126039.67..126039.99 rows=3D126 widt= h=3D164) (actual time=3D7764.696..7768.885 rows=3D38175 loops=3D1) Sort Key: t_s_secyear.customer_id Sort Method: quicksort Memory: 7021kB -> CTE Scan on year_total t_s_secyear (cost=3D= 0.00..126035.28 rows=3D126 width=3D164) (actual time=3D4004.934..7746.174 r= ows=3D38175 loops=3D1) Filter: ((sale_type =3D 's'::text) AND (dy= ear =3D 2002)) Rows Removed by Filter: 346033 -> Sort (cost=3D264676.42..264676.48 rows=3D26 width= =3D104) (actual time=3D112.790..113.328 rows=3D7188 loops=3D1) Sort Key: t_c_firstyear.customer_id Sort Method: quicksort Memory: 687kB -> Hash Join (cost=3D138639.33..264675.81 rows= =3D26 width=3D104) (actual time=3D83.825..109.977 rows=3D7188 loops=3D1) Hash Cond: (t_c_secyear.customer_id =3D t_= c_firstyear.customer_id) -> CTE Scan on year_total t_c_secyear (c= ost=3D0.00..126035.28 rows=3D126 width=3D52) (actual time=3D20.337..42.859 = rows=3D27177 loops=3D1) Filter: ((sale_type =3D 'c'::text) A= ND (dyear =3D 2002)) Rows Removed by Filter: 357031 -> Hash (cost=3D138638.80..138638.80 row= s=3D42 width=3D52) (actual time=3D63.460..63.461 rows=3D26314 loops=3D1) Buckets: 32768 (originally 1024) Ba= tches: 1 (originally 1) Memory Usage: 1725kB -> CTE Scan on year_total t_c_first= year (cost=3D0.00..138638.80 rows=3D42 width=3D52) (actual time=3D28.367..= 59.710 rows=3D26314 loops=3D1) Filter: ((year_total > '0'::nu= meric) AND (sale_type =3D 'c'::text) AND (dyear =3D 2001)) Rows Removed by Filter: 357894 -> Hash (cost=3D403316.74..403316.74 rows=3D6 width=3D156)= (actual time=3D182.649..182.650 rows=3D479 loops=3D1) Buckets: 1024 Batches: 1 Memory Usage: 59kB -> Merge Join (cost=3D403316.35..403316.74 rows=3D6 = width=3D156) (actual time=3D177.380..182.540 rows=3D479 loops=3D1) Merge Cond: (t_s_firstyear.customer_id =3D t_w_f= irstyear.customer_id) -> Sort (cost=3D138639.93..138640.04 rows=3D42= width=3D52) (actual time=3D75.347..77.869 rows=3D37909 loops=3D1) Sort Key: t_s_firstyear.customer_id Sort Method: quicksort Memory: 3273kB -> CTE Scan on year_total t_s_firstyear = (cost=3D0.00..138638.80 rows=3D42 width=3D52) (actual time=3D0.002..60.630 = rows=3D37923 loops=3D1) Filter: ((year_total > '0'::numeric)= AND (sale_type =3D 's'::text) AND (dyear =3D 2001)) Rows Removed by Filter: 346285 -> Sort (cost=3D264676.42..264676.48 rows=3D26= width=3D104) (actual time=3D102.001..102.053 rows=3D1306 loops=3D1) Sort Key: t_w_firstyear.customer_id Sort Method: quicksort Memory: 139kB -> Hash Join (cost=3D138639.33..264675.8= 1 rows=3D26 width=3D104) (actual time=3D93.653..101.516 rows=3D1306 loops= =3D1) Hash Cond: (t_w_secyear.customer_id = =3D t_w_firstyear.customer_id) -> CTE Scan on year_total t_w_secye= ar (cost=3D0.00..126035.28 rows=3D126 width=3D52) (actual time=3D33.759..4= 0.504 rows=3D11252 loops=3D1) Filter: ((sale_type =3D 'w'::t= ext) AND (dyear =3D 2002)) Rows Removed by Filter: 372956 -> Hash (cost=3D138638.80..138638.= 80 rows=3D42 width=3D52) (actual time=3D59.875..59.875 rows=3D11324 loops= =3D1) Buckets: 16384 (originally 102= 4) Batches: 1 (originally 1) Memory Usage: 763kB -> CTE Scan on year_total t_w= _firstyear (cost=3D0.00..138638.80 rows=3D42 width=3D52) (actual time=3D48= .632..58.272 rows=3D11324 loops=3D1) Filter: ((year_total > '= 0'::numeric) AND (sale_type =3D 'w'::text) AND (dyear =3D 2001)) Rows Removed by Filter: = 372884 Planning Time: 6.118 ms Execution Time: 8179.205 ms (98 rows) The execution time is reduced by 99%. Similarly to my previous reports, I w= onder if we can optimize the logic somewhere to improve the performance. Environment: The default configurations of PostgreSQL incur the error: "ERROR: could no= t resize shared memory segment "/PostgreSQL.3539600020" to 2097152 bytes: N= o space left on device" Therefore, to run this query, I set up "work_mem =3D 1GB" 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@127.0.0.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_SEZPR06MB6494F6A2837995BDD4E0BF9A8A5F2SEZPR06MB6494apcp_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi all,

Please see this case:


Query 4 on TPC-DS benchmark:

with year_total as (
 select c_customer_id customer_id
       ,c_first_name customer_first_name
       ,c_last_name customer_last_name
       ,c_preferred_cust_flag customer_preferred_cust_f= lag
       ,c_birth_country customer_birth_country
       ,c_login customer_login
       ,c_email_address customer_email_address
       ,d_year dyear
       ,sum(((ss_ext_list_price-ss_ext_wholesale_cost-s= s_ext_discount_amt)+ss_ext_sales_price)/2) year_total
       ,'s' sale_type
 from customer
     ,store_sales
     ,date_dim
 where c_customer_sk =3D ss_customer_sk
   and ss_sold_date_sk =3D d_date_sk
 group by c_customer_id
         ,c_first_name
         ,c_last_name
         ,c_preferred_cust_flag
         ,c_birth_country
         ,c_login
         ,c_email_address
         ,d_year
 union all
 select c_customer_id customer_id
       ,c_first_name customer_first_name
       ,c_last_name customer_last_name
       ,c_preferred_cust_flag customer_preferred_cust_f= lag
       ,c_birth_country customer_birth_country
       ,c_login customer_login
       ,c_email_address customer_email_address
       ,d_year dyear
       ,sum((((cs_ext_list_price-cs_ext_wholesale_cost-= cs_ext_discount_amt)+cs_ext_sales_price)/2) ) year_total
       ,'c' sale_type
 from customer
     ,catalog_sales
     ,date_dim
 where c_customer_sk =3D cs_bill_customer_sk
   and cs_sold_date_sk =3D d_date_sk
 group by c_customer_id
         ,c_first_name
         ,c_last_name
         ,c_preferred_cust_flag
         ,c_birth_country
         ,c_login
         ,c_email_address
         ,d_year
union all
 select c_customer_id customer_id
       ,c_first_name customer_first_name
       ,c_last_name customer_last_name
       ,c_preferred_cust_flag customer_preferred_cust_f= lag
       ,c_birth_country customer_birth_country
       ,c_login customer_login
       ,c_email_address customer_email_address
       ,d_year dyear
       ,sum((((ws_ext_list_price-ws_ext_wholesale_cost-= ws_ext_discount_amt)+ws_ext_sales_price)/2) ) year_total
       ,'w' sale_type
 from customer
     ,web_sales
     ,date_dim
 where c_customer_sk =3D ws_bill_customer_sk
   and ws_sold_date_sk =3D d_date_sk
 group by c_customer_id
         ,c_first_name
         ,c_last_name
         ,c_preferred_cust_flag
         ,c_birth_country
         ,c_login
         ,c_email_address
         ,d_year
         )
  select  
                  t_s_secyear.= customer_id
                 ,t_s_secyear.= customer_first_name
                 ,t_s_secyear.= customer_last_name
                 ,t_s_secyear.= customer_email_address
 from year_total t_s_firstyear
     ,year_total t_s_secyear
     ,year_total t_c_firstyear
     ,year_total t_c_secyear
     ,year_total t_w_firstyear
     ,year_total t_w_secyear
 where t_s_secyear.customer_id =3D t_s_firstyear.customer_id
   and t_s_firstyear.customer_id =3D t_c_secyear.customer_id
   and t_s_firstyear.customer_id =3D t_c_firstyear.customer_id
   and t_s_firstyear.customer_id =3D t_w_firstyear.customer_id
   and t_s_firstyear.customer_id =3D t_w_secyear.customer_id
   and t_s_firstyear.sale_type =3D 's'
   and t_c_firstyear.sale_type =3D 'c'
   and t_w_firstyear.sale_type =3D 'w'
   and t_s_secyear.sale_type =3D 's'
   and t_c_secyear.sale_type =3D 'c'
   and t_w_secyear.sale_type =3D 'w'
   and t_s_firstyear.dyear =3D  2001
   and t_s_secyear.dyear =3D 2001+1
   and t_c_firstyear.dyear =3D  2001
   and t_c_secyear.dyear =3D  2001+1
   and t_w_firstyear.dyear =3D 2001
   and t_w_secyear.dyear =3D 2001+1
   and t_s_firstyear.year_total > 0
   and t_c_firstyear.year_total > 0
   and t_w_firstyear.year_total > 0
   and case when t_c_firstyear.year_total > 0 then t_c_secyear= .year_total / t_c_firstyear.year_total else null end
           > case when t_s_firstyear.year_= total > 0 then t_s_secyear.year_total / t_s_firstyear.year_total else nu= ll end
   and case when t_c_firstyear.year_total > 0 then t_c_secyear= .year_total / t_c_firstyear.year_total else null end
           > case when t_w_firstyear.year_= total > 0 then t_w_secyear.year_total / t_w_firstyear.year_total else nu= ll end
 order by t_s_secyear.customer_id
         ,t_s_secyear.customer_first_name
         ,t_s_secyear.customer_last_name
         ,t_s_secyear.customer_email_address
limit 100;





The execution time is more than 50 minutes:
                     = ;                     &nb= sp;                     &= nbsp;                    =                     &nbs= p;                     &n= bsp;                     =                      = ;    QUERY PLAN               =                      = ;                     &nb= sp;                                   &nbs= p;                     &n= bsp;                     =                      = ;                
---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------------------------------------------------------------
 Limit  (cost=3D1255378.56..1255378.57 rows=3D1 width=3D132) (act= ual time=3D3024403.311..3024403.342 rows=3D8 loops=3D1)
   CTE year_total
     ->  Append  (cost=3D197433.23..461340.62 r= ows=3D5041142 width=3D216) (actual time=3D4126.043..7897.747 rows=3D384208 = loops=3D1)
           ->  HashAggregate  (c= ost=3D197433.23..233436.60 rows=3D2880270 width=3D216) (actual time=3D4126.= 042..4231.703 rows=3D190581 loops=3D1)
                 Group Key: cu= stomer.c_customer_id, customer.c_first_name, customer.c_last_name, customer= .c_preferred_cust_flag, customer.c_birth_country, customer.c_login, custome= r.c_email_address, date_dim.d_year
                 Batches: 1 &n= bsp;Memory Usage: 213017kB
                 ->  H= ash Join  (cost=3D8151.60..103824.45 rows=3D2880270 width=3D174) (actu= al time=3D69.110..1686.608 rows=3D2685453 loops=3D1)
                     = ;  Hash Cond: (store_sales.ss_sold_date_sk =3D date_dim.d_date_sk)
                     = ;  ->  Hash Join  (cost=3D5103.00..93214.72 rows=3D288027= 0 width=3D174) (actual time=3D49.517..1162.567 rows=3D2750652 loops=3D1)
                     = ;        Hash Cond: (store_sales.ss_customer_sk =3D cus= tomer.c_customer_sk)
                     = ;        ->  Seq Scan on store_sales  (cos= t=3D0.00..80550.70 rows=3D2880270 width=3D30) (actual time=3D0.018..208.022= rows=3D2880404 loops=3D1)
                     = ;        ->  Hash  (cost=3D3853.00..3853.0= 0 rows=3D100000 width=3D152) (actual time=3D49.271..49.271 rows=3D100000 lo= ops=3D1)
                     = ;              Buckets: 131072  Bat= ches: 1  Memory Usage: 17161kB
                     = ;              ->  Seq Scan on c= ustomer  (cost=3D0.00..3853.00 rows=3D100000 width=3D152) (actual time= =3D0.011..26.448 rows=3D100000 loops=3D1)
                     = ;  ->  Hash  (cost=3D2135.49..2135.49 rows=3D73049 width= =3D8) (actual time=3D19.369..19.370 rows=3D73049 loops=3D1)
                     = ;        Buckets: 131072  Batches: 1  Memory = Usage: 3878kB
                     = ;        ->  Seq Scan on date_dim  (cost= =3D0.00..2135.49 rows=3D73049 width=3D8) (actual time=3D0.037..11.763 rows= =3D73049 loops=3D1)
           ->  HashAggregate  (c= ost=3D114410.03..132428.63 rows=3D1441488 width=3D216) (actual time=3D2369.= 202..2447.868 rows=3D136978 loops=3D1)
                 Group Key: cu= stomer_1.c_customer_id, customer_1.c_first_name, customer_1.c_last_name, cu= stomer_1.c_preferred_cust_flag, customer_1.c_birth_country, customer_1.c_lo= gin, customer_1.c_email_address, date_dim_1.d_year
                 Batches: 1 &n= bsp;Memory Usage: 131097kB
                 ->  H= ash Join  (cost=3D8151.60..67561.67 rows=3D1441488 width=3D177) (actua= l time=3D62.483..974.143 rows=3D1430939 loops=3D1)
                     = ;  Hash Cond: (catalog_sales.cs_sold_date_sk =3D date_dim_1.d_date_sk)=
                     = ;  ->  Hash Join  (cost=3D5103.00..60728.94 rows=3D144148= 8 width=3D177) (actual time=3D46.571..687.972 rows=3D1434519 loops=3D1)
                     = ;        Hash Cond: (catalog_sales.cs_bill_customer_sk = =3D customer_1.c_customer_sk)
                     = ;        ->  Seq Scan on catalog_sales  (c= ost=3D0.00..51841.88 rows=3D1441488 width=3D33) (actual time=3D0.029..128.2= 38 rows=3D1441548 loops=3D1)
                     = ;        ->  Hash  (cost=3D3853.00..3853.0= 0 rows=3D100000 width=3D152) (actual time=3D46.311..46.325 rows=3D100000 lo= ops=3D1)
                     = ;              Buckets: 131072  Bat= ches: 1  Memory Usage: 17161kB
                     = ;              ->  Seq Scan on c= ustomer customer_1  (cost=3D0.00..3853.00 rows=3D100000 width=3D152) (= actual time=3D0.005..23.350 rows=3D100000 loops=3D1)
                     = ;  ->  Hash  (cost=3D2135.49..2135.49 rows=3D73049 width= =3D8) (actual time=3D15.677..15.677 rows=3D73049 loops=3D1)
                     = ;        Buckets: 131072  Batches: 1  Memory = Usage: 3878kB
                     = ;        ->  Seq Scan on date_dim date_dim_1 &n= bsp;(cost=3D0.00..2135.49 rows=3D73049 width=3D8) (actual time=3D0.015..7.9= 57 rows=3D73049 loops=3D1)
           ->  HashAggregate  (c= ost=3D61277.38..70269.68 rows=3D719384 width=3D216) (actual time=3D1166.953= ..1198.730 rows=3D56649 loops=3D1)
                 Group Key: cu= stomer_2.c_customer_id, customer_2.c_first_name, customer_2.c_last_name, cu= stomer_2.c_preferred_cust_flag, customer_2.c_birth_country, customer_2.c_lo= gin, customer_2.c_email_address, date_dim_2.d_year
                 Batches: 1 &n= bsp;Memory Usage: 57369kB
                 ->  H= ash Join  (cost=3D8151.60..37897.40 rows=3D719384 width=3D177) (actual= time=3D68.327..508.594 rows=3D719119 loops=3D1)
                     = ;  Hash Cond: (web_sales.ws_sold_date_sk =3D date_dim_2.d_date_sk)
                     = ;  ->  Hash Join  (cost=3D5103.00..32960.30 rows=3D719384= width=3D177) (actual time=3D52.240..357.963 rows=3D719217 loops=3D1)
                     = ;        Hash Cond: (web_sales.ws_bill_customer_sk =3D = customer_2.c_customer_sk)
                     = ;        ->  Seq Scan on web_sales  (cost= =3D0.00..25968.84 rows=3D719384 width=3D33) (actual time=3D0.032..62.464 ro= ws=3D719384 loops=3D1)
                     = ;        ->  Hash  (cost=3D3853.00..3853.0= 0 rows=3D100000 width=3D152) (actual time=3D51.959..51.960 rows=3D100000 lo= ops=3D1)
                     = ;              Buckets: 131072  Bat= ches: 1  Memory Usage: 17161kB
                     = ;              ->  Seq Scan on c= ustomer customer_2  (cost=3D0.00..3853.00 rows=3D100000 width=3D152) (= actual time=3D0.004..25.350 rows=3D100000 loops=3D1)
                     = ;  ->  Hash  (cost=3D2135.49..2135.49 rows=3D73049 width= =3D8) (actual time=3D15.831..15.834 rows=3D73049 loops=3D1)
                     = ;        Buckets: 131072  Batches: 1  Memory = Usage: 3878kB
                     = ;        ->  Seq Scan on date_dim date_dim_2 &n= bsp;(cost=3D0.00..2135.49 rows=3D73049 width=3D8) (actual time=3D0.014..8.1= 00 rows=3D73049 loops=3D1)
   ->  Sort  (cost=3D794037.94..794037.95 rows=3D1 w= idth=3D132) (actual time=3D3024403.310..3024403.313 rows=3D8 loops=3D1)
         Sort Key: t_s_secyear.customer_id, t_s_se= cyear.customer_first_name, t_s_secyear.customer_last_name, t_s_secyear.cust= omer_email_address
         Sort Method: quicksort  Memory: 26kB=
         ->  Nested Loop  (cost=3D0.0= 0..794037.93 rows=3D1 width=3D132) (actual time=3D354851.431..3024403.218 r= ows=3D8 loops=3D1)
               Join Filter: ((t_s_s= ecyear.customer_id =3D t_w_secyear.customer_id) AND (CASE WHEN (t_c_firstye= ar.year_total > '0'::numeric) THEN (t_c_secyear.year_total / t_c_firstye= ar.year_total) ELSE NULL::numeric END > CASE WHEN (t_w_firstyear.year_to= tal > '0'::numeric) THEN (t_w_secyear.year_total / t_w_firstyear.year_total) ELS= E NULL::numeric END))
               Rows Removed by Join= Filter: 810136
               ->  Nested L= oop  (cost=3D0.00..668006.23 rows=3D1 width=3D308) (actual time=3D3355= 4.075..3021248.646 rows=3D72 loops=3D1)
                     = ;Join Filter: ((t_s_secyear.customer_id =3D t_c_secyear.customer_id) AND (C= ASE WHEN (t_c_firstyear.year_total > '0'::numeric) THEN (t_c_secyear.yea= r_total / t_c_firstyear.year_total) ELSE NULL::numeric END > CASE WHEN (= t_s_firstyear.year_total > '0'::numeric) THEN (t_s_secyear.year_total / t_s_firstyear.year_total= ) ELSE NULL::numeric END))
                     = ;Rows Removed by Join Filter: 11876277
                     = ;->  Nested Loop  (cost=3D0.00..541974.53 rows=3D1 width=3D320= ) (actual time=3D14866.104..3001271.961 rows=3D437 loops=3D1)
                     = ;      Join Filter: (t_s_firstyear.customer_id =3D t_s_secye= ar.customer_id)
                     = ;      Rows Removed by Join Filter: 44702488
                     = ;      ->  Nested Loop  (cost=3D0.00..415941.57= rows=3D2 width=3D156) (actual time=3D11739.944..2946020.749 rows=3D1171 lo= ops=3D1)
                     = ;            Join Filter: (t_s_firstyear.cust= omer_id =3D t_w_firstyear.customer_id)
                     = ;            Rows Removed by Join Filter: 112= 695277
                     = ;            ->  Nested Loop  (c= ost=3D0.00..277302.08 rows=3D9 width=3D104) (actual time=3D8139.729..235173= 3.795 rows=3D9952 loops=3D1)
                     = ;                  Join Filter= : (t_s_firstyear.customer_id =3D t_c_firstyear.customer_id)
                     = ;                  Rows Remove= d by Join Filter: 997895870
                     = ;                  ->  = ;CTE Scan on year_total t_s_firstyear  (cost=3D0.00..138631.41 rows=3D= 42 width=3D52) (actual time=3D4126.046..4234.598 rows=3D37923 loops=3D1)
                     = ;                     &nb= sp;  Filter: ((year_total > '0'::numeric) AND (sale_type =3D 's'::t= ext) AND (dyear =3D 2001))
                     = ;                     &nb= sp;  Rows Removed by Filter: 346285
                     = ;                  ->  = ;CTE Scan on year_total t_c_firstyear  (cost=3D0.00..138631.41 rows=3D= 42 width=3D52) (actual time=3D28.926..60.356 rows=3D26314 loops=3D37923)
                     = ;                     &nb= sp;  Filter: ((year_total > '0'::numeric) AND (sale_type =3D 'c'::t= ext) AND (dyear =3D 2001))
                     = ;                     &nb= sp;  Rows Removed by Filter: 357894
                     = ;            ->  CTE Scan on year_tot= al t_w_firstyear  (cost=3D0.00..138631.41 rows=3D42 width=3D52) (actua= l time=3D49.572..59.057 rows=3D11324 loops=3D9952)
                     = ;                  Filter: ((y= ear_total > '0'::numeric) AND (sale_type =3D 'w'::text) AND (dyear =3D 2= 001))
                     = ;                  Rows Remove= d by Filter: 372884
                     = ;      ->  CTE Scan on year_total t_s_secyear  = (cost=3D0.00..126028.55 rows=3D126 width=3D164) (actual time=3D0.002..44.94= 9 rows=3D38175 loops=3D1171)
                     = ;            Filter: ((sale_type =3D 's'::tex= t) AND (dyear =3D 2002))
                     = ;            Rows Removed by Filter: 346033
                     = ;->  CTE Scan on year_total t_c_secyear  (cost=3D0.00..126028.= 55 rows=3D126 width=3D52) (actual time=3D21.023..44.097 rows=3D27177 loops= =3D437)
                     = ;      Filter: ((sale_type =3D 'c'::text) AND (dyear =3D 200= 2))
                     = ;      Rows Removed by Filter: 357031
               ->  CTE Scan= on year_total t_w_secyear  (cost=3D0.00..126028.55 rows=3D126 width= =3D52) (actual time=3D36.137..43.090 rows=3D11252 loops=3D72)
                     = ;Filter: ((sale_type =3D 'w'::text) AND (dyear =3D 2002))
                     = ;Rows Removed by Filter: 372956
 Planning Time: 4.529 ms
 Execution Time: 3024486.695 ms
(83 rows)




If we disable this checking:
diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/= util/pathnode.c
index c42742d2c7..d47d0f0e59 100644
--- a/src/backend/optimizer/util/pathnode.c
+++ b/src/backend/optimizer/util/pathnode.c
@@ -555,16 +555,6 @@ add_path(RelOptInfo *parent_rel, Path *new_path)
                     = ;                     &nb= sp;     }
                     = ;                     &nb= sp;     break;
                     = ;                   case COSTS= _BETTER2:
-                     &nb= sp;                     &= nbsp;   if (keyscmp !=3D PATHKEYS_BETTER1)
-                     &nb= sp;                     &= nbsp;   {
-                     &nb= sp;                     &= nbsp;           outercmp =3D bms_subset_compare(PA= TH_REQ_OUTER(new_path),
-                     &nb= sp;                     &= nbsp;                    =                     &nbs= p;                     &n= bsp;          PATH_REQ_OUTER(old_path));
-                     &nb= sp;                     &= nbsp;           if ((outercmp =3D=3D BMS_EQUAL ||<= /div>
-                     &nb= sp;                     &= nbsp;                    = outercmp =3D=3D BMS_SUBSET2) &&
-                     &nb= sp;                     &= nbsp;                   new_pa= th->rows >=3D old_path->rows &&
-                     &nb= sp;                     &= nbsp;                   new_pa= th->parallel_safe <=3D old_path->parallel_safe)
-                     &nb= sp;                     &= nbsp;                   accept= _new =3D false; /* old dominates new */
-                     &nb= sp;                     &= nbsp;   }
                     = ;                     &nb= sp;     break;
                     = ;                   case COSTS= _DIFFERENT:
 

The execution time is reduced to around 8 seconds:
                     = ;                     &nb= sp;                     &= nbsp;                    =                     &nbs= p;                     &n= bsp;                     =                      = ;                     &nb= sp;                     &= nbsp;                    =                     &nbs= p;                     &nbs= p;  QUERY PLAN                =                     &nbs= p;                     &n= bsp;                     =                      = ;                     &nb= sp;                     &= nbsp;                    =                     &nbs= p;                     &n= bsp;                                 &nbs= p;                     &n= bsp;              
---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------
 Limit  (cost=3D1255392.10..1255392.10 rows=3D1 width=3D132) (act= ual time=3D8068.870..8068.887 rows=3D8 loops=3D1)
   CTE year_total
     ->  Append  (cost=3D197441.91..461358.17 r= ows=3D5041411 width=3D216) (actual time=3D4004.918..7547.884 rows=3D384208 = loops=3D1)
           ->  HashAggregate  (c= ost=3D197441.91..233447.56 rows=3D2880452 width=3D216) (actual time=3D4004.= 917..4112.220 rows=3D190581 loops=3D1)
                 Group Key: cu= stomer.c_customer_id, customer.c_first_name, customer.c_last_name, customer= .c_preferred_cust_flag, customer.c_birth_country, customer.c_login, custome= r.c_email_address, date_dim.d_year
                 Batches: 1 &n= bsp;Memory Usage: 213017kB
                 ->  H= ash Join  (cost=3D8151.60..103827.22 rows=3D2880452 width=3D174) (actu= al time=3D73.291..1594.391 rows=3D2685453 loops=3D1)
                     = ;  Hash Cond: (store_sales.ss_sold_date_sk =3D date_dim.d_date_sk)
                     = ;  ->  Hash Join  (cost=3D5103.00..93217.01 rows=3D288045= 2 width=3D174) (actual time=3D52.407..1100.642 rows=3D2750652 loops=3D1)
                     = ;        Hash Cond: (store_sales.ss_customer_sk =3D cus= tomer.c_customer_sk)
                     = ;        ->  Seq Scan on store_sales  (cos= t=3D0.00..80552.52 rows=3D2880452 width=3D30) (actual time=3D0.017..213.629= rows=3D2880404 loops=3D1)
                     = ;        ->  Hash  (cost=3D3853.00..3853.0= 0 rows=3D100000 width=3D152) (actual time=3D52.083..52.083 rows=3D100000 lo= ops=3D1)
                     = ;              Buckets: 131072  Bat= ches: 1  Memory Usage: 17161kB
                     = ;              ->  Seq Scan on c= ustomer  (cost=3D0.00..3853.00 rows=3D100000 width=3D152) (actual time= =3D0.013..28.104 rows=3D100000 loops=3D1)
                     = ;  ->  Hash  (cost=3D2135.49..2135.49 rows=3D73049 width= =3D8) (actual time=3D20.589..20.592 rows=3D73049 loops=3D1)
                     = ;        Buckets: 131072  Batches: 1  Memory = Usage: 3878kB
                     = ;        ->  Seq Scan on date_dim  (cost= =3D0.00..2135.49 rows=3D73049 width=3D8) (actual time=3D0.036..12.464 rows= =3D73049 loops=3D1)
           ->  HashAggregate  (c= ost=3D114414.19..132433.87 rows=3D1441575 width=3D216) (actual time=3D2186.= 304..2263.365 rows=3D136978 loops=3D1)
                 Group Key: cu= stomer_1.c_customer_id, customer_1.c_first_name, customer_1.c_last_name, cu= stomer_1.c_preferred_cust_flag, customer_1.c_birth_country, customer_1.c_lo= gin, customer_1.c_email_address, date_dim_1.d_year
                 Batches: 1 &n= bsp;Memory Usage: 131097kB
                 ->  H= ash Join  (cost=3D8151.60..67563.00 rows=3D1441575 width=3D177) (actua= l time=3D63.765..880.282 rows=3D1430939 loops=3D1)
                     = ;  Hash Cond: (catalog_sales.cs_sold_date_sk =3D date_dim_1.d_date_sk)=
                     = ;  ->  Hash Join  (cost=3D5103.00..60730.04 rows=3D144157= 5 width=3D177) (actual time=3D47.735..619.585 rows=3D1434519 loops=3D1)
                     = ;        Hash Cond: (catalog_sales.cs_bill_customer_sk = =3D customer_1.c_customer_sk)
                     = ;        ->  Seq Scan on catalog_sales  (c= ost=3D0.00..51842.75 rows=3D1441575 width=3D33) (actual time=3D0.033..121.7= 38 rows=3D1441548 loops=3D1)
                     = ;        ->  Hash  (cost=3D3853.00..3853.0= 0 rows=3D100000 width=3D152) (actual time=3D47.433..47.433 rows=3D100000 lo= ops=3D1)
                     = ;              Buckets: 131072  Bat= ches: 1  Memory Usage: 17161kB
                     = ;              ->  Seq Scan on c= ustomer customer_1  (cost=3D0.00..3853.00 rows=3D100000 width=3D152) (= actual time=3D0.004..23.960 rows=3D100000 loops=3D1)
                     = ;  ->  Hash  (cost=3D2135.49..2135.49 rows=3D73049 width= =3D8) (actual time=3D15.755..15.756 rows=3D73049 loops=3D1)
                     = ;        Buckets: 131072  Batches: 1  Memory = Usage: 3878kB
                     = ;        ->  Seq Scan on date_dim date_dim_1 &n= bsp;(cost=3D0.00..2135.49 rows=3D73049 width=3D8) (actual time=3D0.017..8.0= 30 rows=3D73049 loops=3D1)
           ->  HashAggregate  (c= ost=3D61277.38..70269.68 rows=3D719384 width=3D216) (actual time=3D1121.896= ..1152.982 rows=3D56649 loops=3D1)
                 Group Key: cu= stomer_2.c_customer_id, customer_2.c_first_name, customer_2.c_last_name, cu= stomer_2.c_preferred_cust_flag, customer_2.c_birth_country, customer_2.c_lo= gin, customer_2.c_email_address, date_dim_2.d_year
                 Batches: 1 &n= bsp;Memory Usage: 57369kB
                 ->  H= ash Join  (cost=3D8151.60..37897.40 rows=3D719384 width=3D177) (actual= time=3D63.239..470.700 rows=3D719119 loops=3D1)
                     = ;  Hash Cond: (web_sales.ws_sold_date_sk =3D date_dim_2.d_date_sk)
                     = ;  ->  Hash Join  (cost=3D5103.00..32960.30 rows=3D719384= width=3D177) (actual time=3D47.365..329.499 rows=3D719217 loops=3D1)
                     = ;        Hash Cond: (web_sales.ws_bill_customer_sk =3D = customer_2.c_customer_sk)
                     = ;        ->  Seq Scan on web_sales  (cost= =3D0.00..25968.84 rows=3D719384 width=3D33) (actual time=3D0.036..60.068 ro= ws=3D719384 loops=3D1)
                     = ;        ->  Hash  (cost=3D3853.00..3853.0= 0 rows=3D100000 width=3D152) (actual time=3D47.076..47.078 rows=3D100000 lo= ops=3D1)
                     = ;              Buckets: 131072  Bat= ches: 1  Memory Usage: 17161kB
                     = ;              ->  Seq Scan on c= ustomer customer_2  (cost=3D0.00..3853.00 rows=3D100000 width=3D152) (= actual time=3D0.005..23.909 rows=3D100000 loops=3D1)
                     = ;  ->  Hash  (cost=3D2135.49..2135.49 rows=3D73049 width= =3D8) (actual time=3D15.615..15.615 rows=3D73049 loops=3D1)
                     = ;        Buckets: 131072  Batches: 1  Memory = Usage: 3878kB
                     = ;        ->  Seq Scan on date_dim date_dim_2 &n= bsp;(cost=3D0.00..2135.49 rows=3D73049 width=3D8) (actual time=3D0.019..8.0= 60 rows=3D73049 loops=3D1)
   ->  Sort  (cost=3D794033.93..794033.94 rows=3D1 w= idth=3D132) (actual time=3D8068.869..8068.872 rows=3D8 loops=3D1)
         Sort Key: t_s_secyear.customer_id, t_s_se= cyear.customer_first_name, t_s_secyear.customer_last_name, t_s_secyear.cust= omer_email_address
         Sort Method: quicksort  Memory: 26kB=
         ->  Hash Join  (cost=3D79403= 2.91..794033.92 rows=3D1 width=3D132) (actual time=3D8061.079..8068.861 row= s=3D8 loops=3D1)
               Hash Cond: (t_s_secy= ear.customer_id =3D t_s_firstyear.customer_id)
               Join Filter: ((CASE = WHEN (t_c_firstyear.year_total > '0'::numeric) THEN (t_c_secyear.year_to= tal / t_c_firstyear.year_total) ELSE NULL::numeric END > CASE WHEN (t_s_= firstyear.year_total > '0'::numeric) THEN (t_s_secyear.year_total / t_s_= firstyear.year_total) ELSE NULL::numeric END) AND (CASE WHEN (t_c_firstyear.year_total > '0':= :numeric) THEN (t_c_secyear.year_total / t_c_firstyear.year_total) ELSE NUL= L::numeric END > CASE WHEN (t_w_firstyear.year_total > '0'::numeric) = THEN (t_w_secyear.year_total / t_w_firstyear.year_total) ELSE NULL::numeric END))
               Rows Removed by Join= Filter: 11
               ->  Merge Jo= in  (cost=3D390716.09..390717.01 rows=3D16 width=3D268) (actual time= =3D7877.494..7885.971 rows=3D2793 loops=3D1)
                     = ;Merge Cond: (t_s_secyear.customer_id =3D t_c_secyear.customer_id)
                     = ;->  Sort  (cost=3D126039.67..126039.99 rows=3D126 width=3D164= ) (actual time=3D7764.696..7768.885 rows=3D38175 loops=3D1)
                     = ;      Sort Key: t_s_secyear.customer_id
                     = ;      Sort Method: quicksort  Memory: 7021kB
                     = ;      ->  CTE Scan on year_total t_s_secyear  = (cost=3D0.00..126035.28 rows=3D126 width=3D164) (actual time=3D4004.934..77= 46.174 rows=3D38175 loops=3D1)
                     = ;            Filter: ((sale_type =3D 's'::tex= t) AND (dyear =3D 2002))
                     = ;            Rows Removed by Filter: 346033
                     = ;->  Sort  (cost=3D264676.42..264676.48 rows=3D26 width=3D104)= (actual time=3D112.790..113.328 rows=3D7188 loops=3D1)
                     = ;      Sort Key: t_c_firstyear.customer_id
                     = ;      Sort Method: quicksort  Memory: 687kB
                     = ;      ->  Hash Join  (cost=3D138639.33..264675= .81 rows=3D26 width=3D104) (actual time=3D83.825..109.977 rows=3D7188 loops= =3D1)
                     = ;            Hash Cond: (t_c_secyear.customer= _id =3D t_c_firstyear.customer_id)
                     = ;            ->  CTE Scan on year_tot= al t_c_secyear  (cost=3D0.00..126035.28 rows=3D126 width=3D52) (actual= time=3D20.337..42.859 rows=3D27177 loops=3D1)
                     = ;                  Filter: ((s= ale_type =3D 'c'::text) AND (dyear =3D 2002))
                     = ;                  Rows Remove= d by Filter: 357031
                     = ;            ->  Hash  (cost=3D1= 38638.80..138638.80 rows=3D42 width=3D52) (actual time=3D63.460..63.461 row= s=3D26314 loops=3D1)
                     = ;                  Buckets: 32= 768 (originally 1024)  Batches: 1 (originally 1)  Memory Usage: 1= 725kB
                     = ;                  ->  = ;CTE Scan on year_total t_c_firstyear  (cost=3D0.00..138638.80 rows=3D= 42 width=3D52) (actual time=3D28.367..59.710 rows=3D26314 loops=3D1)
                     = ;                     &nb= sp;  Filter: ((year_total > '0'::numeric) AND (sale_type =3D 'c'::t= ext) AND (dyear =3D 2001))
                     = ;                     &nb= sp;  Rows Removed by Filter: 357894
               ->  Hash &nb= sp;(cost=3D403316.74..403316.74 rows=3D6 width=3D156) (actual time=3D182.64= 9..182.650 rows=3D479 loops=3D1)
                     = ;Buckets: 1024  Batches: 1  Memory Usage: 59kB
                     = ;->  Merge Join  (cost=3D403316.35..403316.74 rows=3D6 width= =3D156) (actual time=3D177.380..182.540 rows=3D479 loops=3D1)
                     = ;      Merge Cond: (t_s_firstyear.customer_id =3D t_w_firsty= ear.customer_id)
                     = ;      ->  Sort  (cost=3D138639.93..138640.04 r= ows=3D42 width=3D52) (actual time=3D75.347..77.869 rows=3D37909 loops=3D1)<= /div>
                     = ;            Sort Key: t_s_firstyear.customer= _id
                     = ;            Sort Method: quicksort  Mem= ory: 3273kB
                     = ;            ->  CTE Scan on year_tot= al t_s_firstyear  (cost=3D0.00..138638.80 rows=3D42 width=3D52) (actua= l time=3D0.002..60.630 rows=3D37923 loops=3D1)
                     = ;                  Filter: ((y= ear_total > '0'::numeric) AND (sale_type =3D 's'::text) AND (dyear =3D 2= 001))
                     = ;                  Rows Remove= d by Filter: 346285
                     = ;      ->  Sort  (cost=3D264676.42..264676.48 r= ows=3D26 width=3D104) (actual time=3D102.001..102.053 rows=3D1306 loops=3D1= )
                     = ;            Sort Key: t_w_firstyear.customer= _id
                     = ;            Sort Method: quicksort  Mem= ory: 139kB
                     = ;            ->  Hash Join  (cos= t=3D138639.33..264675.81 rows=3D26 width=3D104) (actual time=3D93.653..101.= 516 rows=3D1306 loops=3D1)
                     = ;                  Hash Cond: = (t_w_secyear.customer_id =3D t_w_firstyear.customer_id)
                     = ;                  ->  = ;CTE Scan on year_total t_w_secyear  (cost=3D0.00..126035.28 rows=3D12= 6 width=3D52) (actual time=3D33.759..40.504 rows=3D11252 loops=3D1)
                     = ;                     &nb= sp;  Filter: ((sale_type =3D 'w'::text) AND (dyear =3D 2002))
                     = ;                     &nb= sp;  Rows Removed by Filter: 372956
                     = ;                  ->  = ;Hash  (cost=3D138638.80..138638.80 rows=3D42 width=3D52) (actual time= =3D59.875..59.875 rows=3D11324 loops=3D1)
                     = ;                     &nb= sp;  Buckets: 16384 (originally 1024)  Batches: 1 (originally 1) =  Memory Usage: 763kB
                     = ;                     &nb= sp;  ->  CTE Scan on year_total t_w_firstyear  (cost=3D0.= 00..138638.80 rows=3D42 width=3D52) (actual time=3D48.632..58.272 rows=3D11= 324 loops=3D1)
                     = ;                     &nb= sp;        Filter: ((year_total > '0'::numeric) AND = (sale_type =3D 'w'::text) AND (dyear =3D 2001))
                     = ;                     &nb= sp;        Rows Removed by Filter: 372884
 Planning Time: 6.118 ms
 Execution Time: 8179.205 ms
(98 rows)



The execution time is reduced by 99%. Similarly to my previous reports, I w= onder if we can optimize the logic somewhere to improve the performance.


Environment:

The default configurations of PostgreSQL incur the error: "ERROR: &nbs= p;could not resize shared memory segment "/PostgreSQL.3539600020"= to 2097152 bytes: No space left on device"
Therefore, to run this query, I set up "work_mem =3D 1GB"

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_rITZu/view?usp= =3Dsharing

The connection string is: postgresql://ubuntu:ubuntu@127.0.0.1:5432/tpcds&q= uot;

tpch=3D# select version();
                     = ;                     &nb= sp;  version                 &= nbsp;                    =        
---------------------------------------------------------------------------= -----------------------
 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_SEZPR06MB6494F6A2837995BDD4E0BF9A8A5F2SEZPR06MB6494apcp_--