From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Merlin Moncure <mmoncure@gmail.com>
To: Bob Jolliffe <bobjolliffe@gmail.com>
Cc: Peter Geoghegan <pg@bowt.ie>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: How can sort performance be so different
Date: Wed, 06 Feb 2019 09:38:42 +0100
Message-ID: <e615ecfef25d883c811caa58da4a3ad1595b5acf.camel@cybertec.at> (raw)
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>
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
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: pgsql-performance@postgresql.org
Cc: laurenz.albe@cybertec.at, mmoncure@gmail.com, bobjolliffe@gmail.com, pg@bowt.ie, pgsql-performance@lists.postgresql.org
Subject: Re: How can sort performance be so different
In-Reply-To: <e615ecfef25d883c811caa58da4a3ad1595b5acf.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox