Re: How can sort performance be so different - Mailing list pgsql-performance

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