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 1tnzy1-004cq9-DH for pgsql-performance@arkaria.postgresql.org; Fri, 28 Feb 2025 12:59:50 +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 1tnzy1-005aew-E2 for pgsql-performance@arkaria.postgresql.org; Fri, 28 Feb 2025 12:59:48 +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 1tnzy0-005aei-Ih for pgsql-performance@lists.postgresql.org; Fri, 28 Feb 2025 12:59:47 +0000 Received: from cloud.gatewaynet.com ([185.90.37.94]) by makus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1tnzxu-000ALB-1u for pgsql-performance@lists.postgresql.org; Fri, 28 Feb 2025 12:59:46 +0000 Content-Type: multipart/alternative; boundary="------------IPIMkJi9ZE3a2cIjVSzidZCI" Message-ID: <82b7a507-2944-447c-b762-ec65dc2e0047@cloud.gatewaynet.com> Date: Fri, 28 Feb 2025 14:59:38 +0200 MIME-Version: 1.0 Subject: Re: Slow performance of collate "en_US.utf8" To: pgsql-performance@lists.postgresql.org References: Content-Language: en-US From: Achilleas Mantzios - cloud In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------IPIMkJi9ZE3a2cIjVSzidZCI Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: base64 SGkgQWxleGV5DQoNCk9uIDIvMjcvMjUgMTU6NTQsIEFsZXhleSBCb3JzY2hldiB3cm90ZToN Cj4NCj4gSGkgZXZlcnlvbmUhDQo+DQo+IEkgc2VlIHBvb3IgcGVyZm9ybWFuY2Ugb2YgdGV4 dCBzb3J0aW5nIG9mIGNvbGxhdGUgImVuX1VTLnV0ZjgiIGluIFBHIDE3LjQuDQo+DQo+IFRl c3QgcXVlcnk6DQo+DQo+IGV4cGxhaW4gKGFuYWx5emUsIGNvc3RzLCBidWZmZXJzLCB2ZXJi b3NlKQ0KPg0KPiBTRUxFQ1QNCj4NCj4gKCfQkdCy0IHQttCr0LnRj9CZU9GRWXomJCcgfHwg Z2VuLmlkKSBjb2xsYXRlICJlbl9VUy51dGY4Ig0KPg0KPiBGUk9NIGdlbmVyYXRlX3Nlcmll cygxLCAxMDAwMCkgQVMgZ2VuKGlkKQ0KPg0KPiBvcmRlciBieSAxIGRlc2M7DQo+DQo+IEkn dmUgZ290IGV4ZWN1dGlvbiB0aW1lIGxpa2U6DQo+DQo+IEV4ZWN1dGlvbiBUaW1lOiA3My4w NjggbXMNCj4NCj4gU2FtZSBwb29yIHJlc3VsdCB3aXRoIHJ1X1JVLlVURjguDQo+DQo+IFdp dGggb3RoZXIgY29sbGF0aW9ucyB0aW1lIGlzIG11Y2ggYmV0dGVyOg0KPg0KPiBleHBsYWlu IChhbmFseXplLCBjb3N0cywgYnVmZmVycywgdmVyYm9zZSkNCj4NCj4gc2VsZWN0DQo+DQo+ ICgn0JHQstCB0LbQq9C50Y/QmVPRkVl6JiQnIHx8IGdlbi5pZCkgY29sbGF0ZSAiQyINCj4N Cj4gZnJvbSBnZW5lcmF0ZV9zZXJpZXMoMSwgMTAwMDApIEFTIGdlbihpZCkNCj4NCj4gb3Jk ZXIgYnkgMSBkZXNjOw0KPg0KPiBFeGVjdXRpb24gVGltZTogNC43OTIgbXMNCj4NCj4gZXhw bGFpbiAoYW5hbHl6ZSwgY29zdHMsIGJ1ZmZlcnMsIHZlcmJvc2UpDQo+DQo+IFNFTEVDVA0K Pg0KPiAoJ9CR0LLQgdC20KvQudGP0JlT0ZFZeiYkJyB8fCBnZW4uaWQpIGNvbGxhdGUgIkMu dXRmOCINCj4NCj4gRlJPTSBnZW5lcmF0ZV9zZXJpZXMoMSwgMTAwMDApIEFTIGdlbihpZCkN Cj4NCj4gb3JkZXIgYnkgMSBkZXNjOw0KPg0KPiBFeGVjdXRpb24gVGltZTogNy40NzMgbXMN Cj4NCj4gZXhwbGFpbiAoYW5hbHl6ZSwgY29zdHMsIGJ1ZmZlcnMsIHZlcmJvc2UpDQo+DQo+ IHNlbGVjdA0KPg0KPiAoJ9CR0LLQgdC20KvQudGP0JlT0ZFZeiYkJyB8fCBnZW4uaWQpIGNv bGxhdGUgInVuZC14LWljdSINCj4NCj4gZnJvbSBnZW5lcmF0ZV9zZXJpZXMoMSwgMTAwMDAp IEFTIGdlbihpZCkNCj4NCj4gb3JkZXIgYnkgMSBkZXNjOw0KPg0KPiBFeGVjdXRpb24gVGlt ZTogMTMuMjgyIG1zDQo+DQo+IFllcywgY29sbGF0ZSBDIGlzIGZhc3Rlc3QsIElDVSBjb2xs YXRpb25zIGlzIH4gMiBzbG93ZXIgdGhlbiBDLCBidXQgDQo+ICJlbl9VUy51dGY4IiBpcyB+ IDEweCBzbG93ZXIhDQo+DQo+IEkgc3VzcGVjdCBpdCBpcyBzb21lIHBlcmZvcm1hbmNlIGlz c3VlIG92ZXIgdGhlcmUuDQo+DQo+IENhbiBzb21lb25lIG9mIFBHIGhhY2tlcnMgcmVwcm9k dWNlIHRoaXMgcGxlYXNlPw0KPg0KPiAxKSBUaGlzIFBHMTcgaW5zdGFuY2Ugd2FzIGluc3Rh bGxlZCB3aXRoIGRlZmF1bHQgb3B0aW9ucywgYW5kIGluaXRkYiANCj4gZ290IGVuX1VTLnV0 ZjggYXMgc3lzdGVtIGRlZmF1bHQgY29sbGF0aW9uIGFuZCBjcmVhdGVkIFBHIGNsdXN0ZXIg d2l0aCBpdC4NCj4NCj4gSXQgc2VlbXMgbGlrZSBtb3N0IFBHIGRhdGFiYXNlcyBhcmUgY3Jl YXRlZCB0aGlzIHdheSB3aXRoIGVuX1VTLnV0ZjggDQo+IGJ5IGRlZmF1bHQuDQo+DQpJdCBz ZWVtcyB5b3UgaW5pdGlhbGl6ZWQgdGhlIGNsdXN0ZXIgd2l0aCBsaWJjIGFzIHRoZSBsb2Nh bGUgcHJvdmlkZXIuIA0KSGF2ZSB5b3UgdHJpZWQgd2l0aCBpY3UgPw0KPg0KPiAyKSBUeXBp Y2FsIHRleHRcdmFyY2hhciBjb2x1bW5zIGFyZSBjcmVhdGVkIHdpdGggREIgZGVmYXVsdCBl bl9VUy51dGY4IA0KPiBhbmQgcGVyZm9ybXMgcG9vci4NCj4NCj4gZXhwbGFpbiAoYW5hbHl6 ZSwgY29zdHMsIGJ1ZmZlcnMsIHZlcmJvc2UpDQo+DQo+IHNlbGVjdA0KPg0KPiAoJ9CR0LLQ gdC20KvQudGP0JlT0ZFZeiYkJyB8fCBnZW4uaWQpDQo+DQo+IGZyb20gZ2VuZXJhdGVfc2Vy aWVzKDEsIDEwMDAwKSBBUyBnZW4oaWQpDQo+DQo+IG9yZGVyIGJ5IDEgZGVzYzsNCj4NCj4g RXhlY3V0aW9uIFRpbWU6IDczLjYwMCBtcw0KPg0KPiAzKSBUaGUgaW5kZXggc2VhcmNoIG9w ZXJhdGlvbnMgYXJlIGFsc28gc2xvd2VyIHdpdGggZW5fVVMudXRmOCwgYnV0IA0KPiB0aGUg ZGlmZmVyZW5jZSBpcyBub3QgYXMgaGlnaC4NCj4NCj4gUGxlYXNlIHNlZSBhdHRhY2hlZCBm aWxlIHdpdGggdGVzdCB0YWJsZSBhbmQgaW5kZXhlcywgd2l0aCB0ZXN0cyBvbiANCj4gaW5k ZXggcGVyZm9ybWFuY2UuDQo+DQo+IFN5c3RlbSBEZXRhaWxzOg0KPg0KPiBwb3N0Z3Jlc0Bi b3JzY2hldi1wZy1jb3B5ZGIxOn4kIHVuYW1lIC1hIExpbnV4IGJvcnNjaGV2LXBnLWNvcHlk YjEgDQo+IDYuMS4wLTctYW1kNjQgIzEgU01QIFBSRUVNUFRfRFlOQU1JQyBEZWJpYW4gNi4x LjIwLTEgKDIwMjMtMDMtMTkpIA0KPiB4ODZfNjQgR05VL0xpbnV4DQo+DQo+IHBvc3RncmVz QGJvcnNjaGV2LXBnLWNvcHlkYjE6fiQgY2F0IC9ldGMvaXNzdWUgRGViaWFuIEdOVS9MaW51 eCANCj4gdHJpeGllL3NpZCBcbiBcbA0KPg0KPiBzZWxlY3QgdmVyc2lvbigpOw0KPg0KPiBQ b3N0Z3JlU1FMIDE3LjQgKERlYmlhbiAxNy40LTEucGdkZzExMCsyKSBvbiB4ODZfNjQtcGMt bGludXgtZ251LCANCj4gY29tcGlsZWQgYnkgZ2NjIChEZWJpYW4gMTAuMi4xLTYpIDEwLjIu MSAyMDIxMDExMCwgNjQtYml0DQo+DQo+IFNFTEVDVCAqIEZST00gcGdfY29uZmlnKCk7DQo+ DQo+IHxuYW1lIHxzZXR0aW5nIHwNCj4NCj4gfC0tLS0tLS0tLS0tLS0tLS0tfC0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tfA0KPg0KPiB8QklORElSIHwvdXNyL2xp Yi9wb3N0Z3Jlc3FsLzE3L2JpbiB8DQo+DQo+IHxET0NESVIgfC91c3Ivc2hhcmUvZG9jL3Bv c3RncmVzcWwtZG9jLTE3IHwNCj4NCj4gfEhUTUxESVIgfC91c3Ivc2hhcmUvZG9jL3Bvc3Rn cmVzcWwtZG9jLTE3IHwNCj4NCj4gfElOQ0xVREVESVIgfC91c3IvaW5jbHVkZS9wb3N0Z3Jl c3FsIHwNCj4NCj4gfFBLR0lOQ0xVREVESVIgfC91c3IvaW5jbHVkZS9wb3N0Z3Jlc3FsIHwN Cj4NCj4gfElOQ0xVREVESVItU0VSVkVSfC91c3IvaW5jbHVkZS9wb3N0Z3Jlc3FsLzE3L3Nl cnZlciB8DQo+DQo+IHxMSUJESVIgfC91c3IvbGliL3g4Nl82NC1saW51eC1nbnUgfA0KPg0K PiB8UEtHTElCRElSIHwvdXNyL2xpYi9wb3N0Z3Jlc3FsLzE3L2xpYiB8DQo+DQo+IHxMT0NB TEVESVIgfC91c3Ivc2hhcmUvbG9jYWxlIHwNCj4NCj4gfE1BTkRJUiB8L3Vzci9zaGFyZS9w b3N0Z3Jlc3FsLzE3L21hbiB8DQo+DQo+IHxTSEFSRURJUiB8L3Vzci9zaGFyZS9wb3N0Z3Jl c3FsLzE3IHwNCj4NCj4gfFNZU0NPTkZESVIgfC9ldGMvcG9zdGdyZXNxbC1jb21tb24gfA0K Pg0KPiB8UEdYUyB8L3Vzci9saWIvcG9zdGdyZXNxbC8xNy9saWIvcGd4cy9zcmMvbWFrZWZp bGVzL3BneHMubWsgfA0KPg0KPiB8Q09ORklHVVJFIHwgJy0tYnVpbGQ9eDg2XzY0LWxpbnV4 LWdudScgJy0tcHJlZml4PS91c3InIA0KPiAnLS1pbmNsdWRlZGlyPSR7cHJlZml4fS9pbmNs dWRlJyAnLS1tYW5kaXI9JHtwcmVmaXh9L3NoYXJlL21hbicgDQo+ICctLWluZm9kaXI9JHtw cmVmaXh9L3NoYXJlL2luZm8nICctLXN5c2NvbmZkaXI9L2V0YycgDQo+ICctLWxvY2Fsc3Rh dGVkaXI9L3ZhcicgJy0tZGlzYWJsZS1vcHRpb24tY2hlY2tpbmcnIA0KPiAnLS1kaXNhYmxl LXNpbGVudC1ydWxlcycgJy0tbGliZGlyPSR7cHJlZml4fS9saWIveDg2XzY0LWxpbnV4LWdu dScgDQo+ICctLXJ1bnN0YXRlZGlyPS9ydW4nICctLWRpc2FibGUtbWFpbnRhaW5lci1tb2Rl JyANCj4gJy0tZGlzYWJsZS1kZXBlbmRlbmN5LXRyYWNraW5nJyAnLS13aXRoLXRjbCcgJy0t d2l0aC1wZXJsJyANCj4gJy0td2l0aC1weXRob24nICctLXdpdGgtcGFtJyAnLS13aXRoLW9w ZW5zc2wnICctLXdpdGgtbGlieG1sJyANCj4gJy0td2l0aC1saWJ4c2x0JyAnLS1tYW5kaXI9 L3Vzci9zaGFyZS9wb3N0Z3Jlc3FsLzE3L21hbicgDQo+ICctLWRvY2Rpcj0vdXNyL3NoYXJl L2RvYy9wb3N0Z3Jlc3FsLWRvYy0xNycgDQo+ICctLXN5c2NvbmZkaXI9L2V0Yy9wb3N0Z3Jl c3FsLWNvbW1vbicgJy0tZGF0YXJvb3RkaXI9L3Vzci9zaGFyZS8nIA0KPiAnLS1kYXRhZGly PS91c3Ivc2hhcmUvcG9zdGdyZXNxbC8xNycgDQo+ICctLWJpbmRpcj0vdXNyL2xpYi9wb3N0 Z3Jlc3FsLzE3L2JpbicgDQo+ICctLWxpYmRpcj0vdXNyL2xpYi94ODZfNjQtbGludXgtZ251 LycgDQo+ICctLWxpYmV4ZWNkaXI9L3Vzci9saWIvcG9zdGdyZXNxbC8nIA0KPiAnLS1pbmNs dWRlZGlyPS91c3IvaW5jbHVkZS9wb3N0Z3Jlc3FsLycgJy0td2l0aC1leHRyYS12ZXJzaW9u PSAoRGViaWFuIA0KPiAxNy40LTEucGdkZzExMCsyKScgJy0tZW5hYmxlLW5scycgJy0tZW5h YmxlLXRocmVhZC1zYWZldHknIA0KPiAnLS1lbmFibGUtZGVidWcnICctLWRpc2FibGUtcnBh dGgnICctLXdpdGgtdXVpZD1lMmZzJyAnLS13aXRoLWdudS1sZCcgDQo+ICctLXdpdGgtZ3Nz YXBpJyAnLS13aXRoLWxkYXAnICctLXdpdGgtcGdwb3J0PTU0MzInIA0KPiAnLS13aXRoLXN5 c3RlbS10emRhdGE9L3Vzci9zaGFyZS96b25laW5mbycgJ0FXSz1tYXdrJyANCj4gJ01LRElS X1A9L2Jpbi9ta2RpciAtcCcgJ1BST1ZFPS91c3IvYmluL3Byb3ZlJyANCj4gJ1BZVEhPTj0v dXNyL2Jpbi9weXRob24zJyAnVEFSPS9iaW4vdGFyJyAnWFNMVFBST0M9eHNsdHByb2MgLS1u b25ldCcgDQo+ICdDRkxBR1M9LWcgLU8yIC1mc3RhY2stcHJvdGVjdG9yLXN0cm9uZyAtV2Zv cm1hdCANCj4gLVdlcnJvcj1mb3JtYXQtc2VjdXJpdHkgLWZuby1vbWl0LWZyYW1lLXBvaW50 ZXInICdMREZMQUdTPS1XbCwteixyZWxybyANCj4gLVdsLC16LG5vdycgJy0tZW5hYmxlLXRh cC10ZXN0cycgJy0td2l0aC1pY3UnICctLXdpdGgtbGx2bScgDQo+ICdMTFZNX0NPTkZJRz0v dXNyL2Jpbi9sbHZtLWNvbmZpZy0xNicgJ0NMQU5HPS91c3IvYmluL2NsYW5nLTE2JyANCj4g Jy0td2l0aC1sejQnICctLXdpdGgtenN0ZCcgJy0td2l0aC1zeXN0ZW1kJyAnLS13aXRoLXNl bGludXgnIA0KPiAnLS1lbmFibGUtZHRyYWNlJyAnYnVpbGRfYWxpYXM9eDg2XzY0LWxpbnV4 LWdudScgJ0NQUEZMQUdTPS1XZGF0ZS10aW1lIA0KPiAtRF9GT1JUSUZZX1NPVVJDRT0yJyAn Q1hYRkxBR1M9LWcgLU8yIC1mc3RhY2stcHJvdGVjdG9yLXN0cm9uZyANCj4gLVdmb3JtYXQg LVdlcnJvcj1mb3JtYXQtc2VjdXJpdHknfA0KPg0KPiB8Q0MgfGdjYyB8DQo+DQo+IHxDUFBG TEFHUyB8LVdkYXRlLXRpbWUgLURfRk9SVElGWV9TT1VSQ0U9MiAtRF9HTlVfU09VUkNFIA0K PiAtSS91c3IvaW5jbHVkZS9saWJ4bWwyIHwNCj4NCj4gfENGTEFHUyB8LVdhbGwgLVdtaXNz aW5nLXByb3RvdHlwZXMgLVdwb2ludGVyLWFyaXRoIA0KPiAtV2RlY2xhcmF0aW9uLWFmdGVy LXN0YXRlbWVudCAtV2Vycm9yPXZsYSAtV2VuZGlmLWxhYmVscyANCj4gLVdtaXNzaW5nLWZv cm1hdC1hdHRyaWJ1dGUgLVdpbXBsaWNpdC1mYWxsdGhyb3VnaD0zIA0KPiAtV2Nhc3QtZnVu Y3Rpb24tdHlwZSAtV3NoYWRvdz1jb21wYXRpYmxlLWxvY2FsIC1XZm9ybWF0LXNlY3VyaXR5 IA0KPiAtZm5vLXN0cmljdC1hbGlhc2luZyAtZndyYXB2IC1mZXhjZXNzLXByZWNpc2lvbj1z dGFuZGFyZCANCj4gLVduby1mb3JtYXQtdHJ1bmNhdGlvbiAtV25vLXN0cmluZ29wLXRydW5j YXRpb24gLWcgLWcgLU8yIA0KPiAtZnN0YWNrLXByb3RlY3Rvci1zdHJvbmcgLVdmb3JtYXQg LVdlcnJvcj1mb3JtYXQtc2VjdXJpdHkgDQo+IC1mbm8tb21pdC1mcmFtZS1wb2ludGVyIHwN Cj4NCj4gfENGTEFHU19TTCB8LWZQSUMgfA0KPg0KPiB8TERGTEFHUyB8LVdsLC16LHJlbHJv IC1XbCwteixub3cgLUwvdXNyL2xpYi9sbHZtLTE2L2xpYiAtV2wsLS1hcy1uZWVkZWQgfA0K Pg0KPiB8TERGTEFHU19FWCB8IHwNCj4NCj4gfExERkxBR1NfU0wgfCB8DQo+DQo+IHxMSUJT IHwtbHBnY29tbW9uIC1scGdwb3J0IC1sc2VsaW51eCAtbHpzdGQgLWxsejQgLWx4c2x0IC1s eG1sMiAtbHBhbSANCj4gLWxzc2wgLWxjcnlwdG8gLWxnc3NhcGlfa3JiNSAtbHogLWxyZWFk bGluZSAtbHB0aHJlYWQgLWxydCAtbGRsIC1sbSB8DQo+DQo+IHxWRVJTSU9OIHxQb3N0Z3Jl U1FMIDE3LjQgKERlYmlhbiAxNy40LTEucGdkZzExMCsyKQ0KPg0KPiBzZWxlY3QgKiBmcm9t IHBnX2RhdGFiYXNlOw0KPg0KPiB8b2lkIHxkYXRuYW1lIA0KPiB8ZGF0ZGJhfGVuY29kaW5n fGRhdGxvY3Byb3ZpZGVyfGRhdGlzdGVtcGxhdGV8ZGF0YWxsb3djb25ufGRhdGhhc2xvZ2lu ZXZ0fGRhdGNvbm5saW1pdHxkYXRmcm96ZW54aWR8ZGF0bWlubXhpZHxkYXR0YWJsZXNwYWNl fGRhdGNvbGxhdGUgDQo+IHxkYXRjdHlwZSB8ZGF0bG9jYWxlfGRhdGljdXJ1bGVzfGRhdGNv bGx2ZXJzaW9ufGRhdGFjbCB8DQo+DQo+IHwtLS0tLS18LS0tLS0tLS0tfC0tLS0tLXwtLS0t LS0tLXwtLS0tLS0tLS0tLS0tLXwtLS0tLS0tLS0tLS0tfC0tLS0tLS0tLS0tLXwtLS0tLS0t LS0tLS0tLXwtLS0tLS0tLS0tLS18LS0tLS0tLS0tLS0tfC0tLS0tLS0tLS18LS0tLS0tLS0t LS0tLXwtLS0tLS0tLS0tLXwtLS0tLS0tLS0tLXwtLS0tLS0tLS18LS0tLS0tLS0tLS18LS0t LS0tLS0tLS0tLS18LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS18DQo+DQo+ IHw1IHxwb3N0Z3JlcyB8MTAgfDYgfGMgfGZhbHNlIHx0cnVlIHxmYWxzZSB8LTEgfDczMCB8 MSB8MSw2NjMgDQo+IHxlbl9VUy5VVEYtOHxlbl9VUy5VVEYtOHwgfCB8Mi40MCB8IHwNCj4N Cj4gfDE2LDM4OHxkZW1vIHwxMCB8NiB8YyB8ZmFsc2UgfHRydWUgfGZhbHNlIHwtMSB8NzMw IHwxIHwxLDY2MyANCj4gfGVuX1VTLlVURi04fGVuX1VTLlVURi04fCB8IHwyLjQwIHwgfA0K Pg0KPiB8MSB8dGVtcGxhdGUxfDEwIHw2IHxjIHx0cnVlIHx0cnVlIHxmYWxzZSB8LTEgfDcz MCB8MSB8MSw2NjMgDQo+IHxlbl9VUy5VVEYtOHxlbl9VUy5VVEYtOHwgfCB8Mi40MCB8ez1j L3Bvc3RncmVzLHBvc3RncmVzPUNUYy9wb3N0Z3Jlc318DQo+DQo+IHw0IHx0ZW1wbGF0ZTB8 MTAgfDYgfGMgfHRydWUgfGZhbHNlIHxmYWxzZSB8LTEgfDczMCB8MSB8MSw2NjMgDQo+IHxl bl9VUy5VVEYtOHxlbl9VUy5VVEYtOHwgfCB8IHx7PWMvcG9zdGdyZXMscG9zdGdyZXM9Q1Rj L3Bvc3RncmVzfXwNCj4NCj4gQ29sbGF0aW9ucyBhcmUgc3RhbmRhcmQsIG91dC1vZi10aGUt Ym94Og0KPg0KPiBzZWxlY3QgY29sbG5hbWUsIGNvbGxwcm92aWRlciwgY29sbGVuY29kaW5n LCBjb2xsY29sbGF0ZSwgY29sbGN0eXBlLCANCj4gY29sbGxvY2FsZSwNCj4NCj4gY29sbHZl cnNpb24sIGNvbGxpc2RldGVybWluaXN0aWMNCj4NCj4gRlJPTSBwZ19jb2xsYXRpb24gY2xs DQo+DQo+IC0td2hlcmUgY2xsLmNvbGxuYW1lIGluICgnQy51dGY4JywgJ2VuX1VTLnV0Zjgn LCAncnUtUlUteC1pY3UnKQ0KPg0KPiB8Y29sbG5hbWUgfGNvbGxwcm92aWRlcnxjb2xsZW5j b2Rpbmd8Y29sbGNvbGxhdGV8Y29sbGN0eXBlIA0KPiB8Y29sbGxvY2FsZXxjb2xsdmVyc2lv bnxjb2xsaXNkZXRlcm1pbmlzdGljfA0KPg0KPiB8LS0tLS0tLS0tLS18LS0tLS0tLS0tLS0t fC0tLS0tLS0tLS0tLXwtLS0tLS0tLS0tLXwtLS0tLS0tLS0tfC0tLS0tLS0tLS18LS0tLS0t LS0tLS18LS0tLS0tLS0tLS0tLS0tLS0tLXwNCj4NCj4gfGRlZmF1bHQgfGQgfC0xIHwgfCB8 IHwgfHRydWUgfA0KPg0KPiB8QyB8YyB8LTEgfEMgfEMgfCB8IHx0cnVlIHwNCj4NCj4gfFBP U0lYIHxjIHwtMSB8UE9TSVggfFBPU0lYIHwgfCB8dHJ1ZSB8DQo+DQo+IHx1Y3NfYmFzaWMg fGIgfDYgfCB8IHxDIHwxIHx0cnVlIHwNCj4NCj4gfHBnX2NfdXRmOCB8YiB8NiB8IHwgfEMu VVRGLTggfDEgfHRydWUgfA0KPg0KPiB8dW5pY29kZSB8aSB8LTEgfCB8IHx1bmQgfDE1My4x NCB8dHJ1ZSB8DQo+DQo+IHxDLnV0ZjggfGMgfDYgfEMudXRmOCB8Qy51dGY4IHwgfCB8dHJ1 ZSB8DQo+DQo+IHxlbl9VUy51dGY4IHxjIHw2IHxlbl9VUy51dGY4IHxlbl9VUy51dGY4fCB8 Mi40MCB8dHJ1ZSB8DQo+DQo+IHxydV9SVS51dGY4IHxjIHw2IHxydV9SVS51dGY4IHxydV9S VS51dGY4fCB8Mi40MCB8dHJ1ZSB8DQo+DQo+IHxlbl9VUyB8YyB8NiB8ZW5fVVMudXRmOCB8 ZW5fVVMudXRmOHwgfDIuNDAgfHRydWUgfA0KPg0KPiB8cnVfUlUgfGMgfDYgfHJ1X1JVLnV0 ZjggfHJ1X1JVLnV0Zjh8IHwyLjQwIHx0cnVlIHwNCj4NCg== --------------IPIMkJi9ZE3a2cIjVSzidZCI Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable

Hi Alexey

On 2/27/25 15:54, Alexey Borschev wrote:

Hi everyone!

I see poor performance of text sorting of collate "en_US.utf8" in PG 17.4.

Test query:

explain (analyze, costs, buffers, verbose)

SELECT

('=D0=91=D0=B2=D0=81=D0=B6= =D0=AB=D0=B9=D1=8F=D0=99S=D1=91Yz&$' || gen.id) collate "en_US.utf8"

FROM generate_series(= 1, 10000) AS gen(id)

order by 1 desc;

I've= got execution time like:

Execution Time: 73.068 ms

Same poo= r result with ru_RU.UTF8.

With oth= er collations time is much better:

explain (analyze, costs, buffers, verbose)

select

('=D0=91=D0=B2=D0=81=D0=B6= =D0=AB=D0=B9=D1=8F=D0=99S=D1=91Yz&$' || gen.id) collate "C"

from generate_series(= 1, 10000) AS gen(id)

order by 1 desc;

Execution Time: 4.792 ms

explain (analyze, costs, buffers, verbose)

SELECT

('=D0=91=D0=B2=D0=81=D0=B6= =D0=AB=D0=B9=D1=8F=D0=99S=D1=91Yz&$' || gen.id) collate "C.utf8"

FROM generate_series(= 1, 10000) AS gen(id)

order by 1 desc;

Execution Time: 7.473 ms

explain (analyze, costs, buffers, verbose)

select

('=D0=91=D0=B2=D0=81=D0=B6= =D0=AB=D0=B9=D1=8F=D0=99S=D1=91Yz&$' || gen.id) collate "und-x-icu"

=

from generate_series(= 1, 10000) AS gen(id)

order by 1 desc;

Execution Time: 13.282 ms

Yes, col= late C is fastest, ICU collations is ~ 2 slower then C, but "en_US.utf8" is = ~ 10x slower!

I suspect it is some performance issue over t= here.

Can someone of PG hackers reproduce this plea= se?

1) This PG17 instance was installed with defau= lt options, and initdb got en_US.utf8 as system default collation and cre= ated PG cluster with it.

It seems like most PG databases are created t= his way with en_US.utf8 by default.

It seems you initialized the cluster with libc as the locale provider. Have you tried with icu ?

2) Typical text\varchar columns are created wi= th DB default en_US.utf8 and performs poor.

explain (analyze, costs, buffers, verbose)

select

('=D0=91=D0=B2=D0=81=D0=B6= =D0=AB=D0=B9=D1=8F=D0=99S=D1=91Yz&$' || gen.id)

from generate_series(= 1, 10000) AS gen(id)

order by 1 desc;

Execution Time: 73.600 ms

3) The index search operations are also slower= with en_US.utf8, but the difference is not as high.

Please see attached file with test table and = indexes, with tests on index performance.=20

System Details:

postgres@borschev-pg-copydb1:~$ uname -a Linux borschev-pg-copydb1 6.1.0-7-amd64 #1 SMP PREEMPT_DYNAMIC Debian 6.1= =2E20-1 (2023-03-19) x86_64 GNU/Linux

postgres@borschev-pg-copydb1:~$ cat /etc/issue= Debian GNU/Linux trixie/sid \n \l

select version();

PostgreSQL 17.4 (Debian 17.4-1.pgdg110+2= ) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 202101= 10, 64-bit

SELECT * FROM pg_config();

|name = |setting = = = = = = = = = = = = = = = = = = = = = = = |

|-------------= ----|--------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= ---------------------------------------------------------|

|BINDIR= |/usr/lib/postgresql/17/bin = = = = = = = = = = = = = = = = = = = = = = |

|DOCDIR= |/usr/share/doc/postgresql-doc-17 = = = = = = = = = = = = = = = = = = = = = = |

|HTMLDIR |/usr/share/doc/postgresql-doc-17 = = = = = = = = = = = = = = = = = = = = = = |

|INCLUDEDIR |/usr/include/postgresql = = = = = = = = = = = = = = = = = = = = = = = |

|PKGINCLUDEDIR= |/usr/include/postgresql = = = = = = = = = = = = = = = = = = = = = = = |

|INCLUDEDIR-SERVER|/usr/include/postgresql/17/server = = = = = = = = = = = = = = = = = = = = = = = |

|LIBDIR= |/usr/lib/x86_64-linux-gnu = = = = = = = = = = = = = = = = = = = = = = |

|PKGLIBDIR |/usr/lib/postgresql/17/lib = = = = = = = = = = = = = = = = = = = = = = |

|LOCALEDIR |/usr/share/locale = = = = = = = = = = = = = = = = = = = = = = = |

|MANDIR= |/usr/share/postgresql/17/man = = = = = = = = = = = = = = = = = = = = = = |

|SHAREDIR |/usr/share/postgresql/17 = = = = = = = = = = = = = = = = = = = = = = |

|SYSCONFDIR |/etc/postgresql-common = = = = = = = = = = = = = = = = = = = = = = = |

|PGXS = |/usr/lib/postgresql/17/lib/pgxs/src/makefiles/pgxs.mk = = = = = = = = = = = = = = = = = = = = = = |

|CONFIGURE | '--build=3Dx86_64-linux= -gnu' '--prefix=3D/usr' '--includedir=3D${prefi= x}/include' '--mandir=3D${prefix}/s= hare/man' '--infodir=3D${prefix}/= share/info' '--sysconfdir=3D/etc' '--localstatedir=3D/var= ' '--disable-option-check= ing' '--disable-silent-rules= ' '--libdir=3D${prefix}/l= ib/x86_64-linux-gnu' '--runstatedir=3D/run'<= /span> '--disable-maintainer-m= ode' '--disable-dependency-t= racking' '--with-tcl' '--with-perl' '--with-python' = '--with-pam' '--with-openssl'= '--with-libxml' = '--with-libxslt'= '--mandir=3D/usr/share/= postgresql/17/man' '--docdir=3D/usr/share/= doc/postgresql-doc-17' '--sysconfdir=3D/etc/po= stgresql-common' '--datarootdir=3D/usr/s= hare/' '--datadir=3D/usr/share= /postgresql/17' '--bindir=3D/usr/lib/po= stgresql/17/bin' '--libdir=3D/usr/lib/x8= 6_64-linux-gnu/' '--libexecdir=3D/usr/li= b/postgresql/' '--includedir=3D/usr/in= clude/postgresql/' '--with-extra-version=3D= (Debian 17.4-1.pgdg110+2)' '--enable-nls' <= span style=3D"color:#008000;font-weight:bold;">'--enable-thread-safety= ' '--enable-debug'= '--disable-rpath' '--with-uuid=3De2fs' '--with-gnu-ld' = '--with-gssapi' = '--with-ldap' '--with-pgport=3D5432'<= /span> '--with-system-tzdata=3D= /usr/share/zoneinfo' 'AWK=3Dmawk' 'MKDIR_P=3D/bin/mkdir -= p' 'PROVE=3D/usr/bin/prove= ' 'PYTHON=3D/usr/bin/pyth= on3' 'TAR=3D/bin/tar'= 'XSLTPROC=3Dxsltproc --= nonet' 'CFLAGS=3D-g -O2 -fstac= k-protector-strong -Wformat -Werror=3Dformat-security -fno-omit-frame-poi= nter' 'LDFLAGS=3D-Wl,-z,relro= -Wl,-z,now' '--enable-tap-tests' '--with-icu' '--with-llvm' 'LLVM_CONFIG=3D/usr/bin= /llvm-config-16' 'CLANG=3D/usr/bin/clang= -16' '--with-lz4' '--with-zstd' '--with-systemd'= '--with-selinux'= '--enable-dtrace' 'build_alias=3Dx86_64-l= inux-gnu' 'CPPFLAGS=3D-Wdate-time= -D_FORTIFY_SOURCE=3D2' 'CXXFLAGS=3D-g -O2 -fst= ack-protector-strong -Wformat -Werror=3Dformat-security'|

|CC = |gcc = = = = = = = = = = = = = = = = = = = = = = = |

|CPPFLAGS |-Wdate-time -D_FORTIFY_SOURCE=3D2 -D_GNU_SOURCE -I/= usr/include/libxml2 = = = = = = = = = = = = = = = = = = = = = = |

|CFLAGS= |-Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=3Dvla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3D3 -Wcast-function-type -Wshadow=3Dcompatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=3Dstandard -Wno-format-truncation -Wno-stringop-truncation -g -g -O2 -fstack-protector-strong -Wformat -Werror=3Dformat-security -fno-omit-frame-pointer = = = = = = = = = = = = = = = = = |

|CFLAGS_SL |-fPIC = = = = = = = = = = = = = = = = = = = = = = = |

|LDFLAGS |-Wl,-z,relro -Wl,-z,now -L/= usr/lib/llvm-16/lib -Wl,--as-needed = = = = = = = = = = = = = = = = = = = = = = |

|LDFLAGS_EX | = = = = = = = = = = = = = = = = = = = = = = |

|LDFLAGS_SL | = = = = = = = = = = = = = = = = = = = = = = |

|LIBS = |-lpgcommon -lpgport -lselinux -lzstd -llz4 -lxslt -lxml2 -lpam -lssl -lcrypto -lgssapi_krb5 -lz -lreadline -lpthread -lrt -ldl -lm = = = = = = = = = = = = = = = = = = = = = |

|VERSION |PostgreSQL 17.4 (Debian 17.4-1.pgdg110+2)

select * from pg_database;

|oid |datname |datdba|encoding|datlocprovider|datistemplate|datallowconn|dathasloginevt|datconnlimit|datfrozenxid|datminmxid|dattablespace|datcollate |datctype |datlocale|daticurules|datcollversion|datacl = |

|------|------= ---|------|--------|--------------|-------------|------------|-----------= ---|------------|------------|----------|-------------|-----------|------= -----|---------|-----------|--------------|------------------------------= -----|

|5 = |postgres |10 |6 |c |false |true |false |-= 1 |730 |1 |1,= 663 |en_US.UTF-8|= en_US.UTF-8| | |2.40 | = |

|16,388|demo |10 |6 |c |false |true |false |-= 1 |730 |1 |1,= 663 |en_US.UTF-8|= en_US.UTF-8| | |2.40 | = |

|1 = |template1|10 |6 |c |true |true |false |-= 1 |730 |1 |1,= 663 |en_US.UTF-8|= en_US.UTF-8| | |2.40 |{=3Dc/= postgres,postgres=3DCTc/postgres}|

|4 = |template0|10 |6 |c |true |false |false |-= 1 |730 |1 |1,= 663 |en_US.UTF-8|= en_US.UTF-8| | | |{=3Dc/= postgres,postgres=3DCTc/postgres}|

Collations are standard, out-of-the-box:

select collname, collprovider, collencoding, collcollate, collctype, colllocale,

collversion, collisdeterministic

<= p style=3D"margin:0;">FROM pg_collation cll

--where cll.co= llname in ('C.utf8', 'en_US.utf8', 'ru-RU-x-icu')

|collname |collprovider|collencoding|collcollate|collctype |colllocale|collversion|collisdeterministic|

|-----------|-= -----------|------------|-----------|----------|----------|-----------|--= -----------------|

|default |d |-1 | | = | | |true = |

|C = |c |-1 |C |C | | = |true = |

|POSIX = |c |-1 |POSIX |POSIX | | = |true = |

|ucs_basic |b |6 | | = |C |1 |true = |

|pg_c_utf8 |b |6 | | = |C.= UTF-8 |1 |true = |

|unicode |i |-1 | | = |und |153.14 |true = |

|C.utf8 |c |6 |C.= utf8 |C.= utf8 | | |true = |

|en_US.= utf8 |c |6 |en_US.utf8 |en_US.utf8| |2.40 |true = |

|ru_RU.= utf8 |c |6 |ru_RU.utf8 |ru_RU.utf8| |2.40 |true = |

|en_US = |c |6 |en_US.utf8 |en_US.utf8| |2.40 |true = |

|ru_RU = |c |6 |ru_RU.utf8 |ru_RU.utf8| |2.40 |true = |

--------------IPIMkJi9ZE3a2cIjVSzidZCI--