Slow performance of collate "en_US.utf8"
| От | Alexey Borschev |
|---|---|
| Тема | Slow performance of collate "en_US.utf8" |
| Дата | |
| Msg-id | 0bb22133-7c65-42f0-9853-0b65713f85b0@postgrespro.ru обсуждение исходный текст |
| Список | pgsql-performance |
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.
В списке pgsql-performance по дате отправления: