public inbox for [email protected]  

help / color / mirror / Atom feed

From: Alexey Borschev <[email protected]>
To: [email protected]
Subject: Slow performance of collate "en_US.utf8"
Date: Mon, 3 Mar 2025 11:47:07 +0300
Message-ID: <[email protected]> (raw)

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.

view thread (7+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Slow performance of collate "en_US.utf8"
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox