Merlin Moncure wrote:
> yeah, probably. Having said that, I'm really struggling that it can
> take take several minutes to sort such a small number of rows even
> with location issues. I can sort rocks faster than that :-).
>
> Switching between various european collations, I'm seeing subsecond
> sort responses for 44k records on my test box. I don't have the laos
> collation installed unfortunately. Are you seeing kind of penalty in
> other conversions?
I find that it makes a lot of difference what you sort:
CREATE TABLE sort(t text);
INSERT INTO sort SELECT 'ຕົວອັກສອນລາວ... ງ່າຍຂື້ນ' || i FROM generate_series(1, 100000) AS i;
SET work_mem = '1GB';
EXPLAIN (ANALYZE, BUFFERS) SELECT t FROM sort ORDER BY t COLLATE "C";
[...]
Execution Time: 288.752 ms
EXPLAIN (ANALYZE, BUFFERS) SELECT t FROM sort ORDER BY t COLLATE "lo_LA.utf8";
[...]
Execution Time: 47006.683 ms
EXPLAIN (ANALYZE, BUFFERS) SELECT t FROM sort ORDER BY t COLLATE "en_US.utf8";
[...]
Execution Time: 73962.934 ms
CREATE TABLE sort2(t text);
INSERT INTO sort2 SELECT 'this is plain old English' || i FROM generate_series(1, 100000) AS i;
SET work_mem = '1GB';
EXPLAIN (ANALYZE, BUFFERS) SELECT t FROM sort2 ORDER BY t COLLATE "C";
[...]
Execution Time: 237.615 ms
EXPLAIN (ANALYZE, BUFFERS) SELECT t FROM sort2 ORDER BY t COLLATE "lo_LA.utf8";
[...]
Execution Time: 2467.848 ms
EXPLAIN (ANALYZE, BUFFERS) SELECT t FROM sort2 ORDER BY t COLLATE "en_US.utf8";
[...]
Execution Time: 2927.667 ms
This is on my x86_64 Fedora 29 system, kernel 4.20.6, glibc 2.28.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com