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 1tARzr-00Fo5l-15 for pgsql-performance@arkaria.postgresql.org; Mon, 11 Nov 2024 10:50:15 +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 1tARzo-00DJ0r-GB for pgsql-performance@arkaria.postgresql.org; Mon, 11 Nov 2024 10:50:13 +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 1tARzn-00DJ0d-4s for pgsql-performance@lists.postgresql.org; Mon, 11 Nov 2024 10:50:12 +0000 Received: from mail-sg2apc01on2094.outbound.protection.outlook.com ([40.107.215.94] 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 1tARzi-001IKE-6k for pgsql-performance@lists.postgresql.org; Mon, 11 Nov 2024 10:50:09 +0000 ARC-Seal: i=1; a=rsa-sha256; s=arcselector10001; d=microsoft.com; cv=none; b=f8DNR7DD6UDuLM4o9eZ4CgUmleZo00XXv9hhm8CvlVHnIkq/C1+jdpxhbROVpNfRmLPLpCp5jNRV52fA+Y+BMIINGHadQ14wJBusfZq4TjeJG9iRWsYmB03ycbzNpsnJx1GAeNop9K8YrzmDQ3d5yHMbh+QqMn9eVSC++Ci3zUeth/o6K1umUjaKT4dvQo/0zD6wsQGEFvGLFwvIGzAdHK435ulFqvNpVyGZ6n7i2tys4b8xoUh1Y3j1+NIC7uVoQTb704f508NZReO7sL29FAz9vqXFhLI1NQvE4O1V1AOS11Xu9erQ3Y0XoiVoTY23Hx70K5U4s1c1r7ToJvXkIA== 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=8aNjmJZp58kHTGXy3wEIcmXlwXMK9D6BO5DTPN5d9OM=; b=lW5S0B5bvIfU+7i0Ky9GkoMhK1yLOxy/jX0bty8npz9nJ7nR9aFNkBmTvxBafCwGy4aZYXANn4AUsO0WweHdPMwq3RbDB7ZeV7emhNBSrpd7THuOEKT7MMxJE+3qm/rfRV3RXzBthpo54OJyjun/4EZwtAT/eMG9FoWSHBYoIAsTqBDDe3/geBGR5UN7HP6E9ps2grarJ9HUqxtmrlmbLmvC6hd/PqxE30JSUlHvA7ecZlO29aFfgEQ+g+av4dwnKxodNxLSREug0GC65p9TKfeHna3MG0M19tO6A0MSYggdjR820c0+KC1D25njlefwpJptGDv8R2f0/rzjdcVwJg== 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=8aNjmJZp58kHTGXy3wEIcmXlwXMK9D6BO5DTPN5d9OM=; b=NsMjOi1sGFipkdoxUO+QfpdpOUcOq7/KdpjVqtBzkEUJtLJcVXprt5isY9P6kIRSPNms90QU5E/RrS7LIDoqqVLPd5OzSk01he/wMQTLriQJDY3KOMpBQegaUyfYlUAMsox99tUH+6SwwZj5iSdr3jk6sUNDnB809dTJvD0XLwxh+l4D/Rj5l4uMo5vBwPYgT7Senr3S1J9IexzgsU38Kr+hQ33wnR2cuC4K4M0MnkNYwf5Ud72Ob1Js8qft5K75DKHTvcDq+7cNWrbXIqm5Font2X0Jb66cUYk8XaZCctLKQJEwbvcjP1JdZlERZsLCXRU78v2gSQ+5noXv98MVeg== Received: from SEZPR06MB6494.apcprd06.prod.outlook.com (2603:1096:101:18b::13) by SEZPR06MB7291.apcprd06.prod.outlook.com (2603:1096:101:256::9) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.8158.11; Mon, 11 Nov 2024 10:49:57 +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; Mon, 11 Nov 2024 10:49:56 +0000 From: Ba Jinsheng To: Andrei Lepikhov , "pgsql-performance@lists.postgresql.org" Subject: Re: Performance of Query 4 on TPC-DS Benchmark Thread-Topic: Performance of Query 4 on TPC-DS Benchmark Thread-Index: AQHbM53XJabm3wvFF0icxJuGGZYSB7Kx1GeAgAAQ3L8= Date: Mon, 11 Nov 2024 10:49:56 +0000 Message-ID: References: In-Reply-To: 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_|SEZPR06MB7291:EE_ x-ms-office365-filtering-correlation-id: 0e00066c-1b94-4a64-2420-08dd023e950d x-ms-exchange-senderadcheck: 1 x-ms-exchange-antispam-relay: 0 x-microsoft-antispam: BCL:0;ARA:13230040|1800799024|376014|366016|8096899003|38070700018; x-microsoft-antispam-message-info: =?iso-8859-1?Q?uXpzI0+P60lavwT0ngXzH7aCwckmWHK3L+HLEvqCqBkd87sdQ/6ESdWLZb?= =?iso-8859-1?Q?AqwshfS4Wg7rx5+he0HN9kmXCrlHERH9u1FAuPl1uDXNYCKh6PXdKkIela?= =?iso-8859-1?Q?ZCsXfqk4Jm8gZUnNG/ykiVgl4y0tPHkUur82LHpo5xrcpuaNXupgyPMay5?= =?iso-8859-1?Q?tvPBVgkVZXFS5BdQ55m8iVC+wVqoe+LkieO1D5oGRxHapZy6/L0haZ7BmB?= =?iso-8859-1?Q?/St6kUlip4qLgUJkRw0wvoqzsIq/uvrG08zdCC+XEHBlqHA5P8Zsfbw+BA?= =?iso-8859-1?Q?phrleDSyvi/J/Nk6rqm3epD0aqLCpA15wdJV/r8BddmbD33y+PzEqSmyha?= =?iso-8859-1?Q?zcihlVknFJ/e/yw9tzTNiiG8e3PYFArQqF4H/fY9IYPHb17xRYYdCa0ODd?= =?iso-8859-1?Q?o3H15jPZkizd+4zS9FDQzEQcLcHxfu69giGBIe2TZhpo30Hc1IpOV8EJSJ?= =?iso-8859-1?Q?SGLBowti2quOjzcRLOyTAoLSkBUCfFim2LHVB2rMFkohDdbUYP8hx/g1M/?= =?iso-8859-1?Q?ArMcO5ZD4mwiiXWZ1TRfAPQIGifjA+kBHBOEHYxWaHsCIzpqfTJGqXTu+7?= =?iso-8859-1?Q?jS42xSg4ggVG2FD5S+Kgl7c/lCiwV4jX+VwaHE8HCTh6+AY60GJfHtnPB+?= =?iso-8859-1?Q?hEKJ9J6kDGVYUdkoACA99/RsK+4vQ7NrFdSsZFFe9eelctYRGvUUp4teT7?= =?iso-8859-1?Q?PIFaWPR418GtcHuiQWBsNYKdFobjhEpJ1Sh9Lgqz3Ly/1LAYConaq+8yVk?= =?iso-8859-1?Q?yN4ZUH5Uu1m/PzQ59w3RivspWL8SqTb7WWYzNuMRMAHbwaYZQsNXCP9+3J?= =?iso-8859-1?Q?rgcy5U1uF9qFHvxf+cA+CcjavKRxorwL61nD/c6InhfzJ5y3TKqys1+jl8?= =?iso-8859-1?Q?TaFRmY51OFrJO/K+0MyJXYIhpuVPCZm1S1u0fEuCAdgl2jlBKn2vQKKfVC?= =?iso-8859-1?Q?bsnhaI227xzg2NQe6akU1eVQfZPROPeVRBDLvzAjmNuoINvAm6H6Y7nt8D?= =?iso-8859-1?Q?KkN45b/3tCcDQ4riY17ir2K//TITzv6CnKDELh+3CAaVpSrV6YyGDAnQtc?= =?iso-8859-1?Q?bUigKQ2cz5eyij0voq0V0mRC4TZnyR2PFnFsi6jYuATP2go2L3ZUvV0L3O?= =?iso-8859-1?Q?Q0niW44gNkIOo1z9v9KKoyRiDRUQHUmtSpVp0Eoz6FLgvZwn7nLAMPfvLf?= =?iso-8859-1?Q?LPNopxIFQfVh/5sSsppuBc8v2/iDYqAaPj+PD1InM9ySD+PkmJ2Liu+bIH?= =?iso-8859-1?Q?pzPlmnpivXnwbzfez5wo55A/Jf3acs2E+CXp9Fbo7aCCUVriME4araq7Pj?= =?iso-8859-1?Q?wR33/j5EIgsmVERytoPiVGWwD0DanEiXS6SQ6OVO/zadHPe+dZGD6EanIK?= =?iso-8859-1?Q?27MocaqnH0g2oFuUL6i8wHNMSfyvbDqS9EA8Fu1CbUays8oFwJHj0=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)(1800799024)(376014)(366016)(8096899003)(38070700018);DIR:OUT;SFP:1102; x-ms-exchange-antispam-messagedata-chunkcount: 1 x-ms-exchange-antispam-messagedata-0: =?iso-8859-1?Q?3H18MpDv9rdm456/gPiRhKyVSplgrHxZ+GwjdQTD64BjZBoln6A2cyFfIT?= =?iso-8859-1?Q?/YVLYrI2XKkA36kiegGl4to9cCU7VzodJFSKSFjxAa9aAYVHhJnJOXSg61?= =?iso-8859-1?Q?I5DbTy/HFo6X1U4aK1znaOKGoqAR9i7K2YKZ1eFKKPNM3ub/vnsqJ0Kkba?= =?iso-8859-1?Q?tgl+sJ9zKTlQ3yU6lxWusByO21Wj1OXCoqTokvgsHGdg85uvPUey20Nb/0?= =?iso-8859-1?Q?HSuFQEZ3aJc+YFmu4NSAyfz4WE0oQOGEtJoBB8cMlYTqWklgfryMd6Snbt?= =?iso-8859-1?Q?GcrtHZ6gzOrkzwiJRSs+/2Km4VLTr46nBGD0t949LxcvBMNO8fSn5a9aNl?= =?iso-8859-1?Q?uO1c1DZxKGJt9QEHn4+aaFEhYwRbfKGU9JnoC8T45qyMnYPIPIavmpfcYR?= =?iso-8859-1?Q?1VoiMAlX8JdYd+vQIt5fG1ClGE/Um+ynYhPyK0VPwpmzLFlWz9B4I+vq1p?= =?iso-8859-1?Q?tWnjwFehS4n+8Q3HenVEig5dS0fQvxxrcPBNCDOGBefVmNkvYmWGXJ1QOY?= =?iso-8859-1?Q?C7Jdm8kg1d3T5tNeilscrGbSnP4QpTU36sM1e/7u+fiu6bO6OXEk+xHMeU?= =?iso-8859-1?Q?b54XMIuD1TMHXrGx7L0mGarnLh4FGbujrw163nwk7y1qDpyjacuy+hreR4?= =?iso-8859-1?Q?ZMWicmod/lf0wv2QrK/uFKb0ilozYsRTm1Iby4XV4ooaa8H06eEOPIiezi?= =?iso-8859-1?Q?lZIy9+Mz8TKctPKc2TgkE6sy/SuNj+HDgezso4ErNSFW0YgMLO9XiQRY5m?= =?iso-8859-1?Q?1Unygk6c5fpTtpHrTAOwBfQU63cyfku/rOPaF9MzpTipQHH1E/A5+KuwNI?= =?iso-8859-1?Q?OwFktYMXPhNfY6Bv+HskxNUP7LE3vcHLaVF4u6u0SsGDiGaOYUeCFWvPKK?= =?iso-8859-1?Q?20B/SaNTtqD1ixRmqwhs0F++1p5DhG61WBVlXFH5WdgvXHQGrOXp17ptOb?= =?iso-8859-1?Q?a5GNkYA2Sj4G8evv81XmAWoPrAUDRUvvKPubcOAp7lo6p4q5BRrIEWU9Wt?= =?iso-8859-1?Q?hiCa/gaLB8jfA+LmNA7rC9/WBOqtqn+UlCixUDl5ZPDINKzkMjQnGBderR?= =?iso-8859-1?Q?0oxt9KX9dU7NjPMQAtB4/j7FoAa2EpNJPST++fPns0xcTuDGMGWjuof+hm?= =?iso-8859-1?Q?wZRHkQ0J6CmTIHSCzmFa2HRzpQVE2ZFjcvvdobAEY4YyExB1wfz/nSKs0t?= =?iso-8859-1?Q?x6hlf1PPxuE6IsisgSngkOHbhpuD/eT7mvGS2oojTKkcGb479LUMazvk8q?= =?iso-8859-1?Q?jF/e/Hg6+TBqWrfsPRiAncKs4ONoIl6J1KkEyD22N8DSDdfiId4M27eYwq?= =?iso-8859-1?Q?2hibxeb7AdhoumDPkHkuOyHfhNx9iiQx8zo8tH6/lWvsnSMKx0VXWwqP44?= =?iso-8859-1?Q?poiRzQdTsLj3sJDNQtcRgvT9EBkLU455hfUJDhC9O4PS0RWszgwoInMPkj?= =?iso-8859-1?Q?SQMdOBfC4IZqfOUdYXxhykryqHgXD6uQ1M3WANU93ct2berOtMBuNIBmOf?= =?iso-8859-1?Q?B+o7qKw7uUITu4upQgtjlt9RJkMG0NH2UfwH5UR2j6q3sLASimjl6IBi6Y?= =?iso-8859-1?Q?MafA7/o5s5F68y+KnUbQYFQtw/fQdbDNTmYQrpYx6Z3UCbFIDpw8oxuZrR?= =?iso-8859-1?Q?OJiWnhWgjyF+Y=3D?= Content-Type: multipart/alternative; boundary="_000_SEZPR06MB649449C88C484B0BDE17A9C28A582SEZPR06MB6494apcp_" 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: 0e00066c-1b94-4a64-2420-08dd023e950d X-MS-Exchange-CrossTenant-originalarrivaltime: 11 Nov 2024 10:49:56.7940 (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: 9Q7iZ1ooq67daAbkF/s1tI2KKVZWA80AO8GX7Eck6wOn5V3Z/hCFsOLg0p/6BF5Mu+eUZVRjZHfdp27UBKvI8w== X-MS-Exchange-Transport-CrossTenantHeadersStamped: SEZPR06MB7291 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_SEZPR06MB649449C88C484B0BDE17A9C28A582SEZPR06MB6494apcp_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable >It is all the time a challenge for PostgreSQL to estimate such a filter >because of absent information on joint column distribution. >Can you research this way by building extended statistics on these >clauses? It could move the plan to the more optimal direction. Thanks a lot for your effort to analyze this issue, and we really appreciat= e your suggestions! Currently, we focus on exposing these issues that affe= ct performance. In the future, we may consider to look into such a directio= n as you suggested. > Have you tried any tools to improve the cardinality yet, like aqo [0]? Yes, but it takes nearly 1 hour to run this query at a time, so I only run = "EXPLAIN ANALYZE" once, and the performance seems slightly improved. QUERY PLAN Limit (cost=3D293880.50..293880.50 rows=3D1 width=3D132) (actual time=3D2= 527921.078..2527921.233 rows=3D8 loops=3D1) CTE year_total -> Gather (cost=3D115049.92..233367.07 rows=3D384208 width=3D216) (a= ctual time=3D1116.139..4005.105 rows=3D384208 loops=3D1) Workers Planned: 2 Workers Launched: 2 -> Parallel Append (cost=3D114049.92..193946.27 rows=3D160087 = width=3D216) (actual time=3D2430.791..2510.131 rows=3D128069 loops=3D3) -> HashAggregate (cost=3D190763.57..193145.83 rows=3D190= 581 width=3D216) (actual time=3D3977.521..4070.200 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_birt= h_country, customer.c_login, customer.c_email_address, date_dim.d_year Worker 1: Batches: 1 Memory Usage: 120857kB -> Hash Join (cost=3D8151.60..103486.35 rows=3D268= 5453 width=3D174) (actual time=3D64.667..1605.601 rows=3D2685453 loops=3D1) Hash Cond: (store_sales.ss_sold_date_sk =3D da= te_dim.d_date_sk) -> Hash Join (cost=3D5103.00..93216.88 rows= =3D2750652 width=3D174) (actual time=3D48.111..1121.801 rows=3D2750652 loop= s=3D1) Hash Cond: (store_sales.ss_customer_sk = =3D customer.c_customer_sk) -> Seq Scan on store_sales (cost=3D0.0= 0..80552.52 rows=3D2880404 width=3D30) (actual time=3D0.068..230.529 rows= =3D2880404 loops=3D1) -> Hash (cost=3D3853.00..3853.00 rows= =3D100000 width=3D152) (actual time=3D47.735..47.735 rows=3D100000 loops=3D= 1) Buckets: 131072 Batches: 1 Memor= y Usage: 17161kB -> Seq Scan on customer (cost=3D= 0.00..3853.00 rows=3D100000 width=3D152) (actual time=3D0.012..25.023 rows= =3D100000 loops=3D1) -> Hash (cost=3D2135.49..2135.49 rows=3D7304= 9 width=3D8) (actual time=3D16.242..16.242 rows=3D73049 loops=3D1) Buckets: 131072 Batches: 1 Memory Usag= e: 3878kB -> Seq Scan on date_dim (cost=3D0.00..= 2135.49 rows=3D73049 width=3D8) (actual time=3D0.074..8.744 rows=3D73049 lo= ops=3D1) -> HashAggregate (cost=3D114049.92..115762.15 rows=3D136= 978 width=3D216) (actual time=3D2199.723..2268.851 rows=3D136978 loops=3D1) Group Key: customer_1.c_customer_id, customer_1.c_fi= rst_name, customer_1.c_last_name, customer_1.c_preferred_cust_flag, custome= r_1.c_birth_country, customer_1.c_login, customer_1.c_email_address, date_d= im_1.d_year Worker 0: Batches: 1 Memory Usage: 88089kB -> Hash Join (cost=3D8151.60..67544.41 rows=3D1430= 939 width=3D177) (actual time=3D81.920..911.231 rows=3D1430939 loops=3D1) Hash Cond: (catalog_sales.cs_sold_date_sk =3D = date_dim_1.d_date_sk) -> Hash Join (cost=3D5103.00..60729.97 rows= =3D1434519 width=3D177) (actual time=3D53.469..638.140 rows=3D1434519 loops= =3D1) Hash Cond: (catalog_sales.cs_bill_custom= er_sk =3D customer_1.c_customer_sk) -> Seq Scan on catalog_sales (cost=3D0= .00..51842.75 rows=3D1441548 width=3D33) (actual time=3D0.066..134.023 rows= =3D1441548 loops=3D1) -> Hash (cost=3D3853.00..3853.00 rows= =3D100000 width=3D152) (actual time=3D52.937..52.937 rows=3D100000 loops=3D= 1) Buckets: 131072 Batches: 1 Memor= y Usage: 17161kB -> Seq Scan on customer customer_= 1 (cost=3D0.00..3853.00 rows=3D100000 width=3D152) (actual time=3D0.019..2= 7.549 rows=3D100000 loops=3D1) -> Hash (cost=3D2135.49..2135.49 rows=3D7304= 9 width=3D8) (actual time=3D27.968..27.968 rows=3D73049 loops=3D1) Buckets: 131072 Batches: 1 Memory Usag= e: 3878kB -> Seq Scan on date_dim date_dim_1 (co= st=3D0.00..2135.49 rows=3D73049 width=3D8) (actual time=3D0.099..14.115 row= s=3D73049 loops=3D1) -> HashAggregate (cost=3D61268.33..61976.44 rows=3D56649= width=3D216) (actual time=3D1115.125..1142.838 rows=3D56649 loops=3D1) Group Key: customer_2.c_customer_id, customer_2.c_fi= rst_name, customer_2.c_last_name, customer_2.c_preferred_cust_flag, custome= r_2.c_birth_country, customer_2.c_login, customer_2.c_email_address, date_d= im_2.d_year Batches: 1 Memory Usage: 35865kB -> Hash Join (cost=3D8151.60..37896.96 rows=3D7191= 19 width=3D177) (actual time=3D85.606..491.698 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= =3D719217 width=3D177) (actual time=3D59.536..342.685 rows=3D719217 loops= =3D1) Hash Cond: (web_sales.ws_bill_customer_s= k =3D customer_2.c_customer_sk) -> Seq Scan on web_sales (cost=3D0.00.= .25968.84 rows=3D719384 width=3D33) (actual time=3D0.032..67.592 rows=3D719= 384 loops=3D1) -> Hash (cost=3D3853.00..3853.00 rows= =3D100000 width=3D152) (actual time=3D59.430..59.430 rows=3D100000 loops=3D= 1) Buckets: 131072 Batches: 1 Memor= y Usage: 17161kB -> Seq Scan on customer customer_= 2 (cost=3D0.00..3853.00 rows=3D100000 width=3D152) (actual time=3D0.006..3= 3.826 rows=3D100000 loops=3D1) -> Hash (cost=3D2135.49..2135.49 rows=3D7304= 9 width=3D8) (actual time=3D25.997..25.998 rows=3D73049 loops=3D1) Buckets: 131072 Batches: 1 Memory Usag= e: 3878kB -> Seq Scan on date_dim date_dim_2 (co= st=3D0.00..2135.49 rows=3D73049 width=3D8) (actual time=3D0.016..13.499 row= s=3D73049 loops=3D1) -> Sort (cost=3D60513.43..60513.44 rows=3D1 width=3D132) (actual time= =3D2527921.077..2527921.080 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..60513.42 rows=3D1 width=3D132) (act= ual time=3D388081.669..2527921.053 rows=3D8 loops=3D1) Join Filter: ((t_s_secyear.customer_id =3D t_c_firstyear.cus= tomer_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 > C= ASE WHEN (t_s_firstyear.year_total > '0'::numeric) THEN (t_s_secyear.year_t= otal / 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_f= irstyear.year_total) ELSE NULL::numeric END > CASE WHEN (t_w_firstyear.year= _total > '0'::numeric) THEN (t_w_secyear.year_total / t_w_firstyear.year_to= tal) ELSE NULL::numeric END)) Rows Removed by Join Filter: 1289378 -> Nested Loop (cost=3D0.00..49947.59 rows=3D1 width=3D372= ) (actual time=3D106080.918..2524931.374 rows=3D49 loops=3D1) Join Filter: (t_s_secyear.customer_id =3D t_w_secyear.= customer_id) Rows Removed by Join Filter: 4962083 -> Nested Loop (cost=3D0.00..40342.26 rows=3D1 width= =3D320) (actual time=3D10316.060..2506476.401 rows=3D441 loops=3D1) Join Filter: (t_s_secyear.customer_id =3D t_c_se= cyear.customer_id) Rows Removed by Join Filter: 44298069 -> Nested Loop (cost=3D0.00..30736.94 rows=3D1= width=3D268) (actual time=3D7429.522..2435241.747 rows=3D1630 loops=3D1) Join Filter: (t_s_firstyear.customer_id = =3D t_s_secyear.customer_id) Rows Removed by Join Filter: 165296120 -> Nested Loop (cost=3D0.00..21131.61 ro= ws=3D1 width=3D104) (actual time=3D3984.981..2240540.776 rows=3D4330 loops= =3D1) Join Filter: (t_s_firstyear.customer= _id =3D t_w_firstyear.customer_id) Rows Removed by Join Filter: 4294357= 22 -> CTE Scan on year_total t_s_first= year (cost=3D0.00..10565.72 rows=3D3 width=3D52) (actual time=3D3984.972..= 4058.789 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_w_first= year (cost=3D0.00..10565.72 rows=3D3 width=3D52) (actual time=3D0.001..58.= 349 rows=3D11324 loops=3D37923) Filter: ((year_total > '0'::nu= meric) AND (sale_type =3D 'w'::text) AND (dyear =3D 2001)) Rows Removed by Filter: 372884 -> CTE Scan on year_total t_s_secyear (c= ost=3D0.00..9605.20 rows=3D10 width=3D164) (actual time=3D20.421..42.865 ro= ws=3D38175 loops=3D4330) Filter: ((sale_type =3D 's'::text) A= ND (dyear =3D 2002)) Rows Removed by Filter: 346033 -> CTE Scan on year_total t_c_secyear (cost=3D= 0.00..9605.20 rows=3D10 width=3D52) (actual time=3D5.974..42.155 rows=3D271= 77 loops=3D1630) Filter: ((sale_type =3D 'c'::text) AND (dy= ear =3D 2002)) Rows Removed by Filter: 357031 -> CTE Scan on year_total t_w_secyear (cost=3D0.00..= 9605.20 rows=3D10 width=3D52) (actual time=3D0.002..41.219 rows=3D11252 loo= ps=3D441) Filter: ((sale_type =3D 'w'::text) AND (dyear = =3D 2002)) Rows Removed by Filter: 372956 -> CTE Scan on year_total t_c_firstyear (cost=3D0.00..1056= 5.72 rows=3D3 width=3D52) (actual time=3D8.525..59.572 rows=3D26314 loops= =3D49) Filter: ((year_total > '0'::numeric) AND (sale_type = =3D 'c'::text) AND (dyear =3D 2001)) Rows Removed by Filter: 357894 Planning Time: 17.924 ms Execution Time: 2527936.040 ms (86 rows) 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_SEZPR06MB649449C88C484B0BDE17A9C28A582SEZPR06MB6494apcp_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

>It is all the time a challenge for PostgreSQL to estimate such a filter=
>because of absent information on joint column distribution.
>Can you research this way by building extended statistics on these
>clauses? It could move the plan to the more optimal direction.

Thanks a lot for your effort to analyze this issue, and we really appreciat= e your suggestions!  Currently, we focus on exposing these issues that= affect performance. In the future, we may consider to look into such a dir= ection as you suggested. 


> Have you tried any tools to improve the cardinality yet, like aqo [0]?=
Yes, but it takes nearly 1 hour to run this query at a time, so I only run = "EXPLAIN ANALYZE" once, and the performance seems slightly improv= ed.


              QUERY PLAN     &= nbsp;                    =                     &nbs= p;                     &n= bsp;                     =                      = ;                     &nb= sp;                     &= nbsp;                    =                     &nbs= p;                     &n= bsp;                                 &nbs= p;                
                     = ;                
 Limit  (cost=3D293880.50..293880.50 rows=3D1 width=3D132) (actua= l time=3D2527921.078..2527921.233 rows=3D8 loops=3D1)
   CTE year_total
     ->  Gather  (cost=3D115049.92..233367.07 r= ows=3D384208 width=3D216) (actual time=3D1116.139..4005.105 rows=3D384208 l= oops=3D1)
           Workers Planned: 2
           Workers Launched: 2
           ->  Parallel Append  = (cost=3D114049.92..193946.27 rows=3D160087 width=3D216) (actual time=3D2430= .791..2510.131 rows=3D128069 loops=3D3)
                 ->  H= ashAggregate  (cost=3D190763.57..193145.83 rows=3D190581 width=3D216) = (actual time=3D3977.521..4070.200 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_country, cust= omer.c_login, customer.c_email_address, date_dim.d_year
                     = ;  Worker 1:  Batches: 1  Memory Usage: 120857kB
                     = ;  ->  Hash Join  (cost=3D8151.60..103486.35 rows=3D26854= 53 width=3D174) (actual time=3D64.667..1605.601 rows=3D2685453 loops=3D1)
                     = ;        Hash Cond: (store_sales.ss_sold_date_sk =3D da= te_dim.d_date_sk)
                     = ;        ->  Hash Join  (cost=3D5103.00..9= 3216.88 rows=3D2750652 width=3D174) (actual time=3D48.111..1121.801 rows=3D= 2750652 loops=3D1)
                     = ;              Hash Cond: (store_sales.s= s_customer_sk =3D customer.c_customer_sk)
                     = ;              ->  Seq Scan on s= tore_sales  (cost=3D0.00..80552.52 rows=3D2880404 width=3D30) (actual = time=3D0.068..230.529 rows=3D2880404 loops=3D1)
                     = ;              ->  Hash  (c= ost=3D3853.00..3853.00 rows=3D100000 width=3D152) (actual time=3D47.735..47= .735 rows=3D100000 loops=3D1)
                     = ;                    Buck= ets: 131072  Batches: 1  Memory Usage: 17161kB
                     = ;                    ->= ;  Seq Scan on customer  (cost=3D0.00..3853.00 rows=3D100000 widt= h=3D152) (actual time=3D0.012..25.023 rows=3D100000 loops=3D1)
                     = ;        ->  Hash  (cost=3D2135.49..2135.4= 9 rows=3D73049 width=3D8) (actual time=3D16.242..16.242 rows=3D73049 loops= =3D1)
                     = ;              Buckets: 131072  Bat= ches: 1  Memory Usage: 3878kB
                     = ;              ->  Seq Scan on d= ate_dim  (cost=3D0.00..2135.49 rows=3D73049 width=3D8) (actual time=3D= 0.074..8.744 rows=3D73049 loops=3D1)
                 ->  H= ashAggregate  (cost=3D114049.92..115762.15 rows=3D136978 width=3D216) = (actual time=3D2199.723..2268.851 rows=3D136978 loops=3D1)
                     = ;  Group Key: customer_1.c_customer_id, customer_1.c_first_name, custo= mer_1.c_last_name, customer_1.c_preferred_cust_flag, customer_1.c_birth_cou= ntry, customer_1.c_login, customer_1.c_email_address, date_dim_1.d_year
                     = ;  Worker 0:  Batches: 1  Memory Usage: 88089kB
                     = ;  ->  Hash Join  (cost=3D8151.60..67544.41 rows=3D143093= 9 width=3D177) (actual time=3D81.920..911.231 rows=3D1430939 loops=3D1)
                     = ;        Hash Cond: (catalog_sales.cs_sold_date_sk =3D = date_dim_1.d_date_sk)
                     = ;        ->  Hash Join  (cost=3D5103.00..6= 0729.97 rows=3D1434519 width=3D177) (actual time=3D53.469..638.140 rows=3D1= 434519 loops=3D1)
                     = ;              Hash Cond: (catalog_sales= .cs_bill_customer_sk =3D customer_1.c_customer_sk)
                     = ;              ->  Seq Scan on c= atalog_sales  (cost=3D0.00..51842.75 rows=3D1441548 width=3D33) (actua= l time=3D0.066..134.023 rows=3D1441548 loops=3D1)
                     = ;              ->  Hash  (c= ost=3D3853.00..3853.00 rows=3D100000 width=3D152) (actual time=3D52.937..52= .937 rows=3D100000 loops=3D1)
                     = ;                    Buck= ets: 131072  Batches: 1  Memory Usage: 17161kB
                     = ;                    ->= ;  Seq Scan on customer customer_1  (cost=3D0.00..3853.00 rows=3D= 100000 width=3D152) (actual time=3D0.019..27.549 rows=3D100000 loops=3D1)
                     = ;        ->  Hash  (cost=3D2135.49..2135.4= 9 rows=3D73049 width=3D8) (actual time=3D27.968..27.968 rows=3D73049 loops= =3D1)
                     = ;              Buckets: 131072  Bat= ches: 1  Memory Usage: 3878kB
                     = ;              ->  Seq Scan on d= ate_dim date_dim_1  (cost=3D0.00..2135.49 rows=3D73049 width=3D8) (act= ual time=3D0.099..14.115 rows=3D73049 loops=3D1)
                 ->  H= ashAggregate  (cost=3D61268.33..61976.44 rows=3D56649 width=3D216) (ac= tual time=3D1115.125..1142.838 rows=3D56649 loops=3D1)
                     = ;  Group Key: customer_2.c_customer_id, customer_2.c_first_name, custo= mer_2.c_last_name, customer_2.c_preferred_cust_flag, customer_2.c_birth_cou= ntry, customer_2.c_login, customer_2.c_email_address, date_dim_2.d_year
                     = ;  Batches: 1  Memory Usage: 35865kB
                     = ;  ->  Hash Join  (cost=3D8151.60..37896.96 rows=3D719119= width=3D177) (actual time=3D85.606..491.698 rows=3D719119 loops=3D1)
                     = ;        Hash Cond: (web_sales.ws_sold_date_sk =3D date= _dim_2.d_date_sk)
                     = ;        ->  Hash Join  (cost=3D5103.00..3= 2960.30 rows=3D719217 width=3D177) (actual time=3D59.536..342.685 rows=3D71= 9217 loops=3D1)
                     = ;              Hash Cond: (web_sales.ws_= bill_customer_sk =3D customer_2.c_customer_sk)
                     = ;              ->  Seq Scan on w= eb_sales  (cost=3D0.00..25968.84 rows=3D719384 width=3D33) (actual tim= e=3D0.032..67.592 rows=3D719384 loops=3D1)
                     = ;              ->  Hash  (c= ost=3D3853.00..3853.00 rows=3D100000 width=3D152) (actual time=3D59.430..59= .430 rows=3D100000 loops=3D1)
                     = ;                    Buck= ets: 131072  Batches: 1  Memory Usage: 17161kB
                     = ;                    ->= ;  Seq Scan on customer customer_2  (cost=3D0.00..3853.00 rows=3D= 100000 width=3D152) (actual time=3D0.006..33.826 rows=3D100000 loops=3D1)
                     = ;        ->  Hash  (cost=3D2135.49..2135.4= 9 rows=3D73049 width=3D8) (actual time=3D25.997..25.998 rows=3D73049 loops= =3D1)
                     = ;              Buckets: 131072  Bat= ches: 1  Memory Usage: 3878kB
                     = ;              ->  Seq Scan on d= ate_dim date_dim_2  (cost=3D0.00..2135.49 rows=3D73049 width=3D8) (act= ual time=3D0.016..13.499 rows=3D73049 loops=3D1)
   ->  Sort  (cost=3D60513.43..60513.44 rows=3D1 wid= th=3D132) (actual time=3D2527921.077..2527921.080 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..60513.42 rows=3D1 width=3D132) (actual time=3D388081.669..2527921.053 ro= ws=3D8 loops=3D1)
               Join Filter: ((t_s_s= ecyear.customer_id =3D t_c_firstyear.customer_id) AND (CASE WHEN (t_c_first= year.year_total > '0'::numeric) THEN (t_c_secyear.year_total / t_c_first= year.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 NU= LL::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: 1289378
               ->  Nested L= oop  (cost=3D0.00..49947.59 rows=3D1 width=3D372) (actual time=3D10608= 0.918..2524931.374 rows=3D49 loops=3D1)
                     = ;Join Filter: (t_s_secyear.customer_id =3D t_w_secyear.customer_id)
                     = ;Rows Removed by Join Filter: 4962083
                     = ;->  Nested Loop  (cost=3D0.00..40342.26 rows=3D1 width=3D320)= (actual time=3D10316.060..2506476.401 rows=3D441 loops=3D1)
                     = ;      Join Filter: (t_s_secyear.customer_id =3D t_c_secyear= .customer_id)
                     = ;      Rows Removed by Join Filter: 44298069
                     = ;      ->  Nested Loop  (cost=3D0.00..30736.94 = rows=3D1 width=3D268) (actual time=3D7429.522..2435241.747 rows=3D1630 loop= s=3D1)
                     = ;            Join Filter: (t_s_firstyear.cust= omer_id =3D t_s_secyear.customer_id)
                     = ;            Rows Removed by Join Filter: 165= 296120
                     = ;            ->  Nested Loop  (c= ost=3D0.00..21131.61 rows=3D1 width=3D104) (actual time=3D3984.981..2240540= .776 rows=3D4330 loops=3D1)
                     = ;                  Join Filter= : (t_s_firstyear.customer_id =3D t_w_firstyear.customer_id)
                     = ;                  Rows Remove= d by Join Filter: 429435722
                     = ;                  ->  = ;CTE Scan on year_total t_s_firstyear  (cost=3D0.00..10565.72 rows=3D3= width=3D52) (actual time=3D3984.972..4058.789 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_w_firstyear  (cost=3D0.00..10565.72 rows=3D3= width=3D52) (actual time=3D0.001..58.349 rows=3D11324 loops=3D37923)
                     = ;                     &nb= sp;  Filter: ((year_total > '0'::numeric) AND (sale_type =3D 'w'::t= ext) AND (dyear =3D 2001))
                     = ;                     &nb= sp;  Rows Removed by Filter: 372884
                     = ;            ->  CTE Scan on year_tot= al t_s_secyear  (cost=3D0.00..9605.20 rows=3D10 width=3D164) (actual t= ime=3D20.421..42.865 rows=3D38175 loops=3D4330)
                     = ;                  Filter: ((s= ale_type =3D 's'::text) AND (dyear =3D 2002))
                     = ;                  Rows Remove= d by Filter: 346033
                     = ;      ->  CTE Scan on year_total t_c_secyear  = (cost=3D0.00..9605.20 rows=3D10 width=3D52) (actual time=3D5.974..42.155 ro= ws=3D27177 loops=3D1630)
                     = ;            Filter: ((sale_type =3D 'c'::tex= t) AND (dyear =3D 2002))
                     = ;            Rows Removed by Filter: 357031
                     = ;->  CTE Scan on year_total t_w_secyear  (cost=3D0.00..9605.20= rows=3D10 width=3D52) (actual time=3D0.002..41.219 rows=3D11252 loops=3D44= 1)
                     = ;      Filter: ((sale_type =3D 'w'::text) AND (dyear =3D 200= 2))
                     = ;      Rows Removed by Filter: 372956
               ->  CTE Scan= on year_total t_c_firstyear  (cost=3D0.00..10565.72 rows=3D3 width=3D= 52) (actual time=3D8.525..59.572 rows=3D26314 loops=3D49)
                     = ;Filter: ((year_total > '0'::numeric) AND (sale_type =3D 'c'::text) AND = (dyear =3D 2001))
                     = ;Rows Removed by Filter: 357894
 Planning Time: 17.924 ms
 Execution Time: 2527936.040 ms
(86 rows)
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_SEZPR06MB649449C88C484B0BDE17A9C28A582SEZPR06MB6494apcp_--