Message-ID: <e615ecfef25d883c811caa58da4a3ad1595b5acf.camel@cybertec.at>
Subject: Re: How can sort performance be so different
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Merlin Moncure <mmoncure@gmail.com>, Bob Jolliffe <bobjolliffe@gmail.com>
Cc: Peter Geoghegan <pg@bowt.ie>, pgsql-performance@lists.postgresql.org
Date: Wed, 06 Feb 2019 09:38:42 +0100
In-Reply-To: 
 <CAHyXU0ydRkdS45XTtEgwk+AYORikQt8VePM0m2b2KkXdNfjYtw@mail.gmail.com>
References: 
	<CACd=f9feuR3BkZ65Noce_rc2b5R5GYin_9Gd9UaC_rhOBoch5g@mail.gmail.com>
	 <14004.1548787651@sss.pgh.pa.us>
	 <CACd=f9eV=DjLzoa6CXnHpsp5J+gDEQSJ2uH=_u4W=OZuQZ6LBA@mail.gmail.com>
	 <CAH2-WznhLA6TbVQ4Mc5aNUp7hBxbAn6__Mtf7oPeTn7FeOSfmw@mail.gmail.com>
	 <CACd=f9cO5OcmAeBK1M5a3+t8BO7E91Ki0WaLAxFm=FBbon_HDw@mail.gmail.com>
	 <CAHyXU0ydRkdS45XTtEgwk+AYORikQt8VePM0m2b2KkXdNfjYtw@mail.gmail.com>
Organization: Cybertec
Content-Type: text/plain; charset="UTF-8"
Mime-Version: 1.0
Content-Transfer-Encoding: 8bit
Precedence: bulk

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