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 1tp9bA-001A65-VI for pgsql-performance@arkaria.postgresql.org; Mon, 03 Mar 2025 17:29:01 +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 1tp9b9-00CaBf-Im for pgsql-performance@arkaria.postgresql.org; Mon, 03 Mar 2025 17:28:59 +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 1tp1SE-001ryB-20 for pgsql-performance@lists.postgresql.org; Mon, 03 Mar 2025 08:47:14 +0000 Received: from mail.postgrespro.ru ([93.174.131.139]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1tp1SB-000g85-0r for pgsql-performance@lists.postgresql.org; Mon, 03 Mar 2025 08:47:13 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mx2023; t=1740991627; bh=U5toFtEhi6PHZXy9BSisG8c09t21MoQWnH/A1RHcoGY=; h=Message-ID:Date:User-Agent:To:From:Subject:From; b=A/8Kv8de2Tnw6xlVBC/4x1b0YQzP3lJgBoh+abnFmZMCHShFiVDtyO+baNdLOTJ4c 9bFIeBgmaPy3MJzKG65MDNzpxxvP1CnE4JdeOtyrjBneJlHNjQAQwe3zg05uyfwHi6 VLLv7Os6Gf6jJzWfg07gU9q/O9gfO6CY+aMu04xG0+pJoOFTZgD59PZiwkiEJ/+TDI bsKk/DWWNXuyHjPhLxvLOR48RwrWWPd04wxpywiYvCN6wIGdn4ZIhHLwwhGp3yicN/ IM6Mt2NCzHAgbk8j8PwkNRyDpPTMyzRDbS46wOVP97MroFbNc3A5zoGMq/xlEQWjb1 JI8iSoKOkKQhA== Received: from [172.30.48.154] (unknown [172.30.48.154]) (using TLSv1.3 with cipher TLS_AES_128_GCM_SHA256 (128/128 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits) server-digest SHA256) (Client did not present a certificate) (Authenticated sender: a.borschev@postgrespro.ru) by mail.postgrespro.ru (Postfix/465) with ESMTPSA id 7B504606B2 for ; Mon, 3 Mar 2025 11:47:07 +0300 (MSK) Content-Type: multipart/alternative; boundary="------------XZPsl0MsgFXmguAIRkyBY38X" Message-ID: <0bb22133-7c65-42f0-9853-0b65713f85b0@postgrespro.ru> Date: Mon, 3 Mar 2025 11:47:07 +0300 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird To: pgsql-performance@lists.postgresql.org Content-Language: en-US, ru-RU From: Alexey Borschev Subject: Slow performance of collate "en_US.utf8" X-KSMG-AntiPhishing: NotDetected X-KSMG-AntiSpam-Interceptor-Info: not scanned X-KSMG-AntiSpam-Status: not scanned, disabled by settings X-KSMG-AntiVirus: Kaspersky Secure Mail Gateway, version 2.1.0.7854, bases: 2025/03/03 06:47:00 #27558135 X-KSMG-AntiVirus-Status: NotDetected, skipped X-KSMG-LinksScanning: not scanned, disabled by settings X-KSMG-Message-Action: skipped X-KSMG-Rule-ID: 1 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. --------------XZPsl0MsgFXmguAIRkyBY38X Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit Hi! Thank everyone for Your answers! It is now clear, that it is not PG issue and it will not be fixed anytime soon. I see that with pure numbers sorting en_US.utf8 is still well behind: explain (analyze, costs, buffers, verbose) select gen.id::text collate "C" from generate_series(10000, 20000) AS gen(id) order by 1 desc; -- 3.5 ms explain (analyze, costs, buffers, verbose) select gen.id::text collate "en_US.utf8" from generate_series(10000, 20000) AS gen(id) order by 1 desc; -- 19.8 ms On the other hand, when I add limit 1, the difference become much less for the reasons I do not understand: explain (analyze, costs, buffers, verbose) select gen.id::text collate "C" from generate_series(10000, 20000) AS gen(id) order by 1 desc limit 1; -- 1.82 ms explain (analyze, costs, buffers, verbose) select gen.id::text collate "en_US.utf8" from generate_series(10000, 20000) AS gen(id) order by 1 desc limit 1; -- 2.8 ms In fact, I've got no database issues right now - just benchmarking search speed of b-tree indexes on different columns types - int4, int8, numeric, texts, uuids, and run into this corner case. l hope to make a talk about this on one of the PG conferences some day. --------------XZPsl0MsgFXmguAIRkyBY38X Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit

Hi!

Thank everyone for Your answers!

It is now clear, that it is not PG issue and it will not be fixed anytime soon.

I see that with pure numbers sorting en_US.utf8 is still well behind:

explain (analyze, costs, buffers, verbose)

select gen.id::text collate "C"

from generate_series(10000, 20000) AS gen(id)

order by 1 desc;

-- 3.5 ms

explain (analyze, costs, buffers, verbose)

select gen.id::text collate "en_US.utf8"

from generate_series(10000, 20000) AS gen(id)

order by 1 desc;

-- 19.8 ms

On the other hand, when I add limit 1, the difference become much less for the reasons I do not understand:

explain (analyze, costs, buffers, verbose)

select gen.id::text collate "C"

from generate_series(10000, 20000) AS gen(id)

order by 1 desc

limit 1;

-- 1.82 ms

explain (analyze, costs, buffers, verbose)

select gen.id::text collate "en_US.utf8"

from generate_series(10000, 20000) AS gen(id)

order by 1 desc

limit 1;

-- 2.8 ms

In fact, I've got no database issues right now - just benchmarking search speed of b-tree indexes on different columns types - int4, int8, numeric, texts, uuids, and run into this corner case.

l hope to make a talk about this on one of the PG conferences some day.

--------------XZPsl0MsgFXmguAIRkyBY38X--