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 1uAz7R-004uEw-54 for pgsql-bugs@arkaria.postgresql.org; Fri, 02 May 2025 22:44:34 +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 1uAz7O-00C6N7-Pc for pgsql-bugs@arkaria.postgresql.org; Fri, 02 May 2025 22:44:31 +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 1uAz7O-00C6Mz-Aq for pgsql-bugs@lists.postgresql.org; Fri, 02 May 2025 22:44:31 +0000 Received: from loire.is.ed.ac.uk ([129.215.16.10]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1uAz7M-000m5I-3C for pgsql-bugs@lists.postgresql.org; Fri, 02 May 2025 22:44:30 +0000 Received: from exseed.ed.ac.uk (hbdat4.is.ed.ac.uk [129.215.235.40]) by loire.is.ed.ac.uk (8.15.2/8.15.2) with ESMTPS id 542MiRRg2139254 (version=TLSv1.2 cipher=ECDHE-RSA-AES256-GCM-SHA384 bits=256 verify=NOT); Fri, 2 May 2025 23:44:27 +0100 Received: from hbdkb4.is.ed.ac.uk (129.215.235.39) by hbdat4.is.ed.ac.uk (129.215.235.40) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.2.1748.10; Fri, 2 May 2025 23:44:26 +0100 Received: from EUR05-DB8-obe.outbound.protection.outlook.com (104.47.17.108) by hbdkb4.is.ed.ac.uk (129.215.235.39) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.2.1748.10 via Frontend Transport; Fri, 2 May 2025 23:44:26 +0100 ARC-Seal: i=1; a=rsa-sha256; s=arcselector10001; d=microsoft.com; cv=none; b=qI730qK9m4Z/glZNeVACLYa74g9SE0nmM8VlSVSW7G+E4n+0ZUHLkd2PfNBYJGWHZGJZe8w84en05/Gf8FhlMa7BJLaGBLkE6jjJvWEKJ16Dk7kOCwaaF4+bCCJUcyLWlF2QdhPfqW91ikbBZ2VQB5ZCZFQq4Eg/5mSZaQaowSWD/AZNx34IxRHgDrEUHl/icu1UNz/3RfYcfsZZwIS8cL6OiUf2hEJv+ogO2tURZCpwc0M9yXCUmx/IwiFALOtoZpJ0y7hjLwYFtwgFuFrW0W979LgBum6sD0W7akA8cbtt2FUHkUxAKiAAC4yJ2Y5R2E/UoxNNEEpTW0R0zR4Jig== 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=fTxvNqUZEfQJSozY4SMkU8HwQSp+ol03wyrSUTca6Dc=; b=tuEae2YnFwpqLR7aqWU9yFnMcoHjpFX65u5l0RIru2ObdhO0tb8BxWU/63goZivnWRkldN1JqLKwHDLvbUFnrttDMTntP0vHScoORYZaflDYZ4tBroTu199gQZvO8oqS+Y+zGNZnTV8aYL2yXH/v7D7jnzh7o1jWU9yix1+T2c/j3HjJaPDDPSh3l6cDOtKrplRzP8iWUSCjCs7oOOzKamR1ampptHeVN5NtEsYaaDLaDT0mWOxVzHCzS8zYrdhDF80gUEU578ZjoSwl1Ok0FuaC+xsSFxIlkQOmHkCjOOLYjOjnI5PUudPQb+o1lVcjquzvFmQVHRCaVc0aiFix6Q== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=pass smtp.mailfrom=ed.ac.uk; dmarc=pass action=none header.from=ed.ac.uk; dkim=pass header.d=ed.ac.uk; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ed.ac.uk; s=selector1; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=fTxvNqUZEfQJSozY4SMkU8HwQSp+ol03wyrSUTca6Dc=; b=1cUhwtp6kKMLD00ZzHVKv3iJOM9Jb35OjzcnOd0VybuEEQKRyvo67+gKnQbnYEqu/c3VU2wSGvarurVfwn/kuRZdfjjjSAiCyi0IahvFWz+DKKfzPolrVyNcB0m7jPkJGiJJIMb7TBG+IKXJFhXmBIZNS1TSNhVD/4YAZkfX1zi5c6vmP7D9Bol4YHEX0pNGRoIGl+4WZtBwn6u5TdA3dGnHiIIF5/Yu0Iw3jY0d+uZLGewa0r353DewLpzWQDxB2HWDAs2K+LjkeHZyuL8Hr4AYOw5K2tfk3cjJfAiUfuxEpXiOPgETI3VVMZukJgZagiE2YXrOXUmlhaXkAGPEGw== Received: from AS2PR05MB10442.eurprd05.prod.outlook.com (2603:10a6:20b:605::6) by PAXPR05MB8464.eurprd05.prod.outlook.com (2603:10a6:102:1a7::15) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.8678.31; Fri, 2 May 2025 22:44:20 +0000 Received: from AS2PR05MB10442.eurprd05.prod.outlook.com ([fe80::3fc0:a77f:dda2:e1c7]) by AS2PR05MB10442.eurprd05.prod.outlook.com ([fe80::3fc0:a77f:dda2:e1c7%5]) with mapi id 15.20.8699.019; Fri, 2 May 2025 22:44:20 +0000 From: Sergey Koposov To: "tgl@sss.pgh.pa.us" CC: "pgsql-bugs@lists.postgresql.org" Subject: Re: BUG #18909: Query creates millions of temporary files and stalls Thread-Topic: BUG #18909: Query creates millions of temporary files and stalls Thread-Index: AQHbu5KAmzIBRhKfLUOu2MpwWKoq/7O/55MAgAAIwoA= Date: Fri, 2 May 2025 22:44:19 +0000 Message-ID: References: <18909-e5e1b702c9441b8a@postgresql.org> <2051617.1746223978@sss.pgh.pa.us> In-Reply-To: <2051617.1746223978@sss.pgh.pa.us> Accept-Language: en-GB, en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: user-agent: Evolution 3.52.3-0ubuntu1 authentication-results: dkim=none (message not signed) header.d=none;dmarc=none action=none header.from=ed.ac.uk; x-ms-publictraffictype: Email x-ms-traffictypediagnostic: AS2PR05MB10442:EE_|PAXPR05MB8464:EE_ x-ms-office365-filtering-correlation-id: 96f34154-5a80-44ba-69d8-08dd89cae095 x-ms-exchange-senderadcheck: 1 x-ms-exchange-antispam-relay: 0 x-microsoft-antispam: BCL:0;ARA:13230040|1800799024|366016|376014|10070799003|38070700018; x-microsoft-antispam-message-info: =?utf-8?B?NnlKZ1hJajFGdmRxRXlaazZ6dlRHY1VSTTVzbUVkaFRNTE5SYUYrSUc2VU1W?= =?utf-8?B?bkt1Q1JJSE5zQXhEdE82ZTg4dHJaSXJCNEpMK2RaMlJpL0NZUTNCZGZ4Tnpx?= =?utf-8?B?eDkzcU96SE12M2k3MWxuNWo0cVN1Z29mc2VKV3QwTzBMVjI1UTd0RlNmdG9E?= =?utf-8?B?QnBFL2lVd0M1Z0NFYllpa3ZJbVJGOHltcGVVejQwNmJkV2ZicTJDM3Z4aCtv?= =?utf-8?B?S09tdXowUWMycjRrV2Q4UUpVMGF5Y3orV3N5MTk2SjVPMXFrMTBJNWhEcEI2?= =?utf-8?B?QnpQdEV3YjFJNG9tYk5qbGk3dDhKV3FnUFBMdWxHVjU5VFE0Y2tkUWw5T25T?= =?utf-8?B?a3ZxZ3cza0o3YlFWdGlNcGRZaU15MkZhNzZLOFJ2emEydDRTcmhoQkVrR3Na?= =?utf-8?B?VmVFK2Rab2c1bmxFTk9iMVlNdVF5bkgyQ253dTJmR2R2UTNzME8zTlpVV2o5?= =?utf-8?B?bmdCTVBCM3FwME1kbFpIaUdQenIrU05BKzBXTGY4djZpUFhSV3dkc0VwYjIr?= =?utf-8?B?VjVsdlFVb2pEam55cFZMNFBHNXlCd3RZTlRRbHlpTTc4SjFVN2FYQW1pSUFD?= =?utf-8?B?dUxlN0lCRCs0VlNSOEtSeCtIWGswQ0RSZUdvZG9uQzJ4VGxUa21iWXN6NTZn?= =?utf-8?B?dW5UNnRMczRsc3I1L3RDOFl2bWkxSDgxeEVaamk2VzJ1ZlBDWTQxaElXMEE1?= =?utf-8?B?N0JGUXZvZGRGaGdOWVJjVE9FWUZub0ZYcTQ2QXBYTzNwaEFUeG9GUHgzQVlU?= =?utf-8?B?dWo3Q0Z2cGVoWHJZNWoyNkdHV2tZbXNtVEgrOVEwVE9Qd3lRUmg1MjN3U3pE?= =?utf-8?B?WG82Q001Zi9LTFJZZTFvdW8rQXJoS2FSa1d4U1NwN1JXdm5DVVJBQVpSNkI2?= =?utf-8?B?T3ZKU1RZRVZxcWRiTVNXOWhLOUROUUxDYzZMblQvSWdPMy9iQmxOSmRtR1NI?= =?utf-8?B?NGZYUXZ1WEFMQldxYjZZdnlCbUE0U2tyYWJJU0hJQXRXSFZiZ0kyMXIxTHJ4?= =?utf-8?B?UldJOFpUcjRjdHRDTHRVYnhpT2EvTW1TeFpWMjRqU3RGUWtZK2lvUklOM2VX?= =?utf-8?B?YzhCY1JkdUxyenNwQ09hRE9UM1hUWkEvNG5LdW5ENjA3QnRGeklKWDJzTVZr?= =?utf-8?B?RnRjcjl1ZEJvMkVkZVhoVFU4b05IT2JkRW1MRHhzOWxmYTdudTZjNEJjMmxZ?= =?utf-8?B?YXV6TXJ5b1BNRGZvMUQ1VFFqLzl1aWlaWXA2eDkwb01xZTlKRm8rcFYvMGp5?= =?utf-8?B?ODM5Y3Z4ZmQ0MVZkSmMwOThoNUJidkF6amN0eUFvclJ1NStCd1pYWjJ2YzZy?= =?utf-8?B?aDJIZzF0M0RjbUJuZnBlTWRDTmU1alk2RGtJUlFQQmg3ck81OEU1eFFZWVhx?= =?utf-8?B?dGtHV1Z6cFlKOEVTL2wrZXU4a0ZNZWpWT3hZd2gvZGJjTytiN25aVzBrRTU0?= =?utf-8?B?Vm9rN3VhV0E1cmdNVGk3UlJwN0cwYkhJZWYxeXo4Q0tHYnN1Y0JkVStCQXBU?= =?utf-8?B?MDRtY0QrSTA0ekdEdE54VGZKdWpqVVo1MmplYTdYK3NJUTZsYkZmdnlxRUkv?= =?utf-8?B?dE5MN1EwMkRYenhhbUFkS2JyVWUvZlYvblBmOENBRDR1ZXppbTZWcUZENVJa?= =?utf-8?B?MVk4eW56SlhPNk5KdklRQko5bmcyUmc3Q1gvSXhiVTJYd0Y3M0NDV1lWZmhw?= =?utf-8?B?NEMwVjBMNkNETWNvMnM1VWJOcU1WNmtLNzhSUjhBalUzUnI3ME5XSnR4c2tm?= =?utf-8?B?U2ROM2Q0bXMrVzNYc0tCaThzRDljMnpWMTdyOXZGdC9rTk5UU0d0M2JSeHlW?= =?utf-8?B?RDExRGZHeDlnaVhrVmRlcGZVYi9ZMVNxOElYaXo0SVRYd3o2SjE5VnJBMlli?= =?utf-8?B?aFo5SGV4Wmp5azNyZlZFVjRWWnNla3I3Q0VjWkhrN2JMQ1JjS0hHcjhQS3BG?= =?utf-8?B?R3pZaVI3KzlseDRQR1FVTE9Sd1ZVSWhhaFdDdFA0M1ZjTUczSVdJclRqL3Zj?= =?utf-8?B?ek84V1A2UEZ3PT0=?= x-forefront-antispam-report: CIP:255.255.255.255;CTRY:;LANG:en;SCL:1;SRV:;IPV:NLI;SFV:NSPM;H:AS2PR05MB10442.eurprd05.prod.outlook.com;PTR:;CAT:NONE;SFS:(13230040)(1800799024)(366016)(376014)(10070799003)(38070700018);DIR:OUT;SFP:1102; x-ms-exchange-antispam-messagedata-chunkcount: 1 x-ms-exchange-antispam-messagedata-0: =?utf-8?B?ZlZRUElvdzNveFZiUjJ6QWM0R2l1OXdHUW1pQTYxcXBNWmtyOTdVdmpQQXlH?= =?utf-8?B?enRib0hESkp0UlB0MXNjcWRENFhyeStQMzNOUVhCVDdSRFo3dytxQ2hERE5O?= =?utf-8?B?TXNmZktGRXdvQWJ1R3U1UkNxMkg2WW5MRnY4R1hnOFVJR0U5NmdGNGt1WmI4?= =?utf-8?B?bXNyVnBpM3d3Tks5VU5KRjkzeUtWVVlXMkl2ZHdtYmd0U1BVUDE1YlRsWkNy?= =?utf-8?B?UzM4cndYVEZIUDVMVUYzK3ZzQnRRRnJZaUdWQWJvYjU2d0pRUG1ud3RscjhU?= =?utf-8?B?bjUyZDB6cS9admdUOTdRS2Y1T21UbUM0OXYwVzRFQzJvUDYwN0NjWlNmR1Rl?= =?utf-8?B?SzVMRzhqdEJyYTQwL01zQ0kxaFBDTVRMay9iV1dEV1Zmckd3WHBIQmtvNGpN?= =?utf-8?B?QWhFVkIweU5RREJTSjV6djFxUnpuM0J0MW1nT2lpVTRVNGJZTU9ESENHVnJs?= =?utf-8?B?MEZKTzZaaWNxNE1TUW5iTHl4cEFacUIvMlQ3ekcrcUY0S0JRNS9sRHhvMGRH?= =?utf-8?B?V0tWeTFSOEJ2bHI5V2EyQnJPZUlLRWI5dWN1WWo2eXNTUlZ3VXZPdW1IeTYv?= =?utf-8?B?RGJYQW5idDJGVjRnL21NdDA2Zll5VGJ5WExEVzViWDR5NlM3K29jQUUzSUR4?= =?utf-8?B?SktHT3FDdGdXQXhZOVZpQ0lrZmtaWGlySEJEdS9yaDIvcGdOem1yVk9KM1By?= =?utf-8?B?MEdWWGR0Uk9kdnhKRlVKZ0NhaEVqb21nR1huSC9XNFh4UUprVVdmVEgzbHVo?= =?utf-8?B?Y01IL3FJSGtxaWZwbnJjSEdyZllOUDVldUlybXBJZWltMDRuKzR4SHR0aW5Y?= =?utf-8?B?bDRKWk1TNEJkZGFVMTlySDRGMDVJV1JtTWdjK3A2Nk9tRjJwcXppUk5NKyti?= =?utf-8?B?ZzF4azNYSnovQldMNWdvTjAzVHFmcDVFbkVZaFRqRWxXR0haNGZGYW9mWXE5?= =?utf-8?B?aWw3N1FBQVNWdXVQOC9sVEdhbkFmWG56NUNTM09TK2M1Z05nRTd5RUJxWGFC?= =?utf-8?B?YzRNZldXbXZaUmludGdSSHUxcmhRNUhLb3NGRFVVN1BnQjZvbjhLUG93TVJy?= =?utf-8?B?aUxTb3JEeDhQbWpKQlhmSlQ3ZWoxdjFiOFl2YmRNNFhSTnZoZzdtU25GdVdO?= =?utf-8?B?UUtIT05hQThmd0hCTUNxdTc3YURzUm1xZDgrMWl5a3lhSVViMXdoNnQ2eFVZ?= =?utf-8?B?bE5wQXVoWUdaUXkxZGx0SVFGaHN0YVlnUWdpbkJqbVUvK3hBU245eUlpejZI?= =?utf-8?B?WWZYeitVUGlRbGM0Y09TYVc3c2d5RS9ESW5VS2tSMW1vRzNCWW9YMTlCUVdK?= =?utf-8?B?Q1c5OXducUY2YVl5NnFlNWYxYUJLMnRGM1JpYXZRZmJuL2g4UWZUMlNaK1E0?= =?utf-8?B?VUxPbHYvWkcyeXdJWGxpUDdHZDZ4L1FpcjI2OHkrd0lldHdoT1FKWVFPcFlR?= =?utf-8?B?Z1Nla1E0RVFMVE1aa2lIOExWSlZjQk5acWxIbmJqL2g5SG9NM0FOMythUk1h?= =?utf-8?B?RlRsN2o4c1lmb3JMdGVWSGZCSWFYY0Q1VFFyN2Z1R2d1TkZ1cCtDUisxZGhw?= =?utf-8?B?TS9VMTFPVlpudFRrWHZhelhaY0VPakloZ0l5OEJ6QzFVUG05eVdoamhmNVM5?= =?utf-8?B?T1p6OXBzM1dHWGhxeWd1U3lCLzVoa25YRG85RTQ4NFJ3bnZMUkk2c1BGeGNB?= =?utf-8?B?OGw4d1BxOFdDVnlGcEZINHQxOEVoM3NvZGZhVjRWaFIxVXVHWXFOdFdPZjgv?= =?utf-8?B?RmNHYnNJRUt0SUpoU0xxenlSdkptdUtqQi9BaUR2ZUpiejFiZ1U5U05NRzR6?= =?utf-8?B?aDFuelhicUZPQ25Xd2Yxbm1qWlo0aS9jVzNGRDN1KzhjRjJmSk0xdGpLUmVz?= =?utf-8?B?VjRqWlU1blgvZ1FXeGM4KzgrdTQvQTlRb2h4R0ZRb040cXh3MElzbDVac0dw?= =?utf-8?B?cUM4L2VxaW1VRkE3WHlSL1NmQ2dHcVJXVFVwdWltL210d0p5VGVxR0YrZndI?= =?utf-8?B?bW53MFAzQUdBdnBncld2K1NNbVRjV2FyTHZkbWd3OXAvWVRnRXN5VGtBK2JZ?= =?utf-8?B?aEZEZ3g2aHdVaTB1a2MvbFdsdWJOcmdQampnMlZFUy9laDlzVnY1YWZNcTVi?= =?utf-8?B?SW1jdSt0NnNiMThXUVRmcHhzUExyY3E4YzMyUUZadThlM0VUdGQyNjMvcVBD?= =?utf-8?B?YWJGNXFrb0lpckJIMTh6MjNVSWNuWlFxV0N6aTF1YS9UYXo1WkQ0MlpjU0V0?= =?utf-8?B?N3h6QXNFb2RSbDB2STdQU0QrekRnPT0=?= Content-Type: text/plain; charset="utf-8" Content-ID: Content-Transfer-Encoding: base64 MIME-Version: 1.0 X-MS-Exchange-CrossTenant-AuthAs: Internal X-MS-Exchange-CrossTenant-AuthSource: AS2PR05MB10442.eurprd05.prod.outlook.com X-MS-Exchange-CrossTenant-Network-Message-Id: 96f34154-5a80-44ba-69d8-08dd89cae095 X-MS-Exchange-CrossTenant-originalarrivaltime: 02 May 2025 22:44:20.0232 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: 2e9f06b0-1669-4589-8789-10a06934dc61 X-MS-Exchange-CrossTenant-mailboxtype: HOSTED X-MS-Exchange-CrossTenant-userprincipalname: ZgCmf0a3cXLtDMklqpiOh00+WXBMj2LOHa30oV18Qbh2RWztUqYRxTpeTPoL5ni9SpRfRce7vflfUwI6vMDkSw== X-MS-Exchange-Transport-CrossTenantHeadersStamped: PAXPR05MB8464 X-OriginatorOrg: ed.ac.uk X-Edinburgh-Scanned: at loire.is.ed.ac.uk List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk T24gRnJpLCAyMDI1LTA1LTAyIGF0IDE4OjEyIC0wNDAwLCBUb20gTGFuZSB3cm90ZToNCj4gUEcg QnVnIHJlcG9ydGluZyBmb3JtIDxub3JlcGx5QHBvc3RncmVzcWwub3JnPiB3cml0ZXM6DQo+ID4g SSBoYXZlIGEgcXVlcnk6IC4uLg0KPiA+IHRoYXQgSSByYW4gKGZyb20gcHl0aG9uIGNsaWVudCks IGFuZCBhZnRlciBpdCBkaWRuJ3QgZmluaXNoIGluIH4gYW4gaG91ciwgaQ0KPiA+IHN0YXJ0ZWQg dG8gaW52ZXN0aWdhdGUsIGZpbmRpbmcgdGhhdCB0aGUgcGdzcWxfdG1wIGZvbGRlciBoYWQgMTUg bWlsbGlvbg0KPiA+IHRlbXBvcmFyeSBmaWxlcyBmcm9tIHRoYXQgcXVlcnkuDQo+DQo+IERvZXMg aXQgYmVoYXZlIGFueSBiZXR0ZXIgaWYgeW91IGRpc2FibGUgcGFyYWxsZWxpc20NCj4gKHNldCBt YXhfcGFyYWxsZWxfd29ya2Vyc19wZXJfZ2F0aGVyID0gMCkgPw0KDQpZZXMsIEkndmUganVzdCB0 cmllZCBhbmQgdGhhdCBhbHNvIHByZXZlbnRzIHRoZSBpc3N1ZS4NCg0KICAgUw0KVGhlIFVuaXZl cnNpdHkgb2YgRWRpbmJ1cmdoIGlzIGEgY2hhcml0YWJsZSBib2R5LCByZWdpc3RlcmVkIGluIFNj b3RsYW5kLCB3aXRoIHJlZ2lzdHJhdGlvbiBudW1iZXIgU0MwMDUzMzYuIElzIGUgYnVpZGhlYW5u IGNhcnRoYW5uYWlzIGEgdGjigJkgYW5uIGFuIE9pbHRoaWdoIERow7luIMOIaWRlYW5uLCBjbMOg cmFpY2h0ZSBhbiBBbGJhLCDDoGlyZWFtaCBjbMOgcmFpZGggU0MwMDUzMzYuDQo=