Content-Type: text/plain; charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 10.3 \(3273\))
Subject: Re: 10x faster sort performance on Skylake CPU vs Ivy
 Bridge
From: =?utf-8?Q?Felix_Geisend=C3=B6rfer?= <felix@felixge.de>
In-Reply-To: <793.1503673641@sss.pgh.pa.us>
Date: Sun, 27 Aug 2017 12:56:20 +0200
Cc: pgsql-performance@postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <75CFB1A6-BC3E-4E3E-BC66-5BF7CB8E2C4F@felixge.de>
References: <79C36278-87E4-4F9C-9C34-FA4ECB2B4B49@felixge.de>
 <793.1503673641@sss.pgh.pa.us>
To: Tom Lane <tgl@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


> On Aug 25, 2017, at 17:07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>=20
> =3D?utf-8?Q?Felix_Geisend=3DC3=3DB6rfer?=3D <felix@felixge.de> writes:
>> I recently came across a performance difference between two machines tha=
t surprised me:
>> ...
>> As you can see, Machine A spends 5889ms on the Sort Node vs 609ms on Mac=
hine B when looking at the "Exclusive" time with explain.depesz.com [3][4].=
 I.e. Machine B is ~10x faster at sorting than Machine B (for this particul=
ar query).
>=20
> I doubt this is a hardware issue, it's more likely that you're comparing
> apples and oranges.  The first theory that springs to mind is that the
> sort keys are strings and you're using C locale on the faster machine but
> some non-C locale on the slower.  strcoll() is pretty darn expensive
> compared to strcmp() :-(

You're right, that seems to be it.

Machine A was using strcoll() (lc_collate=3Den_US.UTF-8)
Machine B was using strcmp() (lc_collate=3DC)

After switching Machine A to use lc_collate=3DC, I get:

CTE Scan on zulu  (cost=3D40673.620..40742.300 rows=3D3434 width=3D56) (act=
ual time=3D1368.610..1368.698 rows=3D58 loops=3D1)
  CTE zulu
      ->  HashAggregate  (cost=3D40639.280..40673.620 rows=3D3434 width=3D5=
6) (actual time=3D1368.607..1368.659 rows=3D58 loops=3D1)
              Group Key: mike.two, ((mike.golf)::text)
            ->  Unique  (cost=3D37656.690..40038.310 rows=3D34341 width=3D1=
04) (actual time=3D958.493..1168.128 rows=3D298104 loops=3D1)
                  ->  Sort  (cost=3D37656.690..38450.560 rows=3D317549 widt=
h=3D104) (actual time=3D958.491..1055.635 rows=3D316982 loops=3D1)
                          Sort Key: mike.two, ((mike.lima)::text) COLLATE "=
papa", mike.echo DESC, mike.quebec
                          Sort Method: quicksort  Memory: 56834kB
                        ->  Seq Scan on mike  (cost=3D0.000..8638.080 rows=
=3D317549 width=3D104) (actual time=3D0.043..172.496 rows=3D316982 loops=3D=
1)
                                Filter: (golf five NOT NULL)
                                Rows Removed by Filter: 26426

So Machine A needs 883ms [1] for the sort vs 609ms [2] for Machine B. That'=
s  ~1.4x faster which seems reasonable :).

Sorry for the delayed response, I didn't have access to machine B to confir=
m this right away.

> 			regards, tom lane

This is my first post to a PostgreSQL mailing list, but I've been lurking
for a while. Thank you for taking the time for replying to e-mails such
as mine and all the work you've put into PostgreSQL over the years.
I'm deeply grateful.

> On Aug 25, 2017, at 17:43, Peter Geoghegan <pg@bowt.ie> wrote:
>=20
> On Fri, Aug 25, 2017 at 8:07 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I doubt this is a hardware issue, it's more likely that you're comparing
>> apples and oranges.  The first theory that springs to mind is that the
>> sort keys are strings and you're using C locale on the faster machine but
>> some non-C locale on the slower.  strcoll() is pretty darn expensive
>> compared to strcmp() :-(
>=20
> strcoll() is very noticeably slower on macOS, too.
>=20

Thanks. This immediately explains what I saw when testing this query on a L=
inux machine that was also using lc_collate=3Den_US.UTF-8 but not being slo=
wed down by it as much as the macOS machine.

[1] https://explain.depesz.com/s/LOqa
[2] https://explain.depesz.com/s/zVe

--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance