From: =?utf-8?Q?Felix_Geisend=C3=B6rfer?= <felix@felixge.de>
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Mime-Version: 1.0 (Mac OS X Mail 10.3 \(3273\))
Subject: 10x faster sort performance on Skylake CPU vs Ivy Bridge
Message-Id: <79C36278-87E4-4F9C-9C34-FA4ECB2B4B49@felixge.de>
Date: Fri, 25 Aug 2017 16:12:26 +0200
To: pgsql-performance@postgresql.org
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Hi,

I recently came across a performance difference between two machines that s=
urprised me:

Postgres Version / OS on both machines: v9.6.3 / MacOS 10.12.5

Machine A: MacBook Pro Mid 2012, 2.7 GHz Intel Core i7 (Ivy Bridge), 8 MB L=
3 Cache, 16 GB 1600 MHz DDR3 [1]
Machine B: MacBook Pro Late 2016, 2.6 GHz Intel Core i7 (Skylake), 6 MB L3 =
Cache,16 GB 2133 MHz LPDDR3 [2]

Query Performance on Machine A: [3]

CTE Scan on zulu  (cost=3D40673.620..40742.300 rows=3D3434 width=3D56) (act=
ual time=3D6339.404..6339.462 rows=3D58 loops=3D1)
  CTE zulu
      ->  HashAggregate  (cost=3D40639.280..40673.620 rows=3D3434 width=3D3=
1) (actual time=3D6339.400..6339.434 rows=3D58 loops=3D1)
              Group Key: mike.two, mike.golf
            ->  Unique  (cost=3D37656.690..40038.310 rows=3D34341 width=3D6=
4) (actual time=3D5937.934..6143.161 rows=3D298104 loops=3D1)
                  ->  Sort  (cost=3D37656.690..38450.560 rows=3D317549 widt=
h=3D64) (actual time=3D5937.933..6031.925 rows=3D316982 loops=3D1)
                          Sort Key: mike.two, mike.lima, mike.echo DESC, mi=
ke.quebec
                          Sort Method: quicksort  Memory: 56834kB
                        ->  Seq Scan on mike  (cost=3D0.000..8638.080 rows=
=3D317549 width=3D64) (actual time=3D0.019..142.831 rows=3D316982 loops=3D1)
                                Filter: (golf five NOT NULL)
                                Rows Removed by Filter: 26426

Query Performance on Machine B: [4]

CTE Scan on zulu  (cost=3D40621.420..40690.100 rows=3D3434 width=3D56) (act=
ual time=3D853.436..853.472 rows=3D58 loops=3D1)
  CTE zulu
      ->  HashAggregate  (cost=3D40587.080..40621.420 rows=3D3434 width=3D3=
1) (actual time=3D853.433..853.448 rows=3D58 loops=3D1)
              Group Key: mike.two, mike.golf
            ->  Unique  (cost=3D37608.180..39986.110 rows=3D34341 width=3D6=
4) (actual time=3D634.412..761.678 rows=3D298104 loops=3D1)
                  ->  Sort  (cost=3D37608.180..38400.830 rows=3D317057 widt=
h=3D64) (actual time=3D634.411..694.719 rows=3D316982 loops=3D1)
                          Sort Key: mike.two, mike.lima, mike.echo DESC, mi=
ke.quebec
                          Sort Method: quicksort  Memory: 56834kB
                        ->  Seq Scan on mike  (cost=3D0.000..8638.080 rows=
=3D317057 width=3D64) (actual time=3D0.047..85.534 rows=3D316982 loops=3D1)
                                Filter: (golf five NOT NULL)
                                Rows Removed by Filter: 26426

As you can see, Machine A spends 5889ms on the Sort Node vs 609ms on Machin=
e 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 particular =
query).

My question is: Why?

I understand that this is a 3rd gen CPU vs a 6th gen, and that things have =
gotten faster despite stagnant clock speeds, but seeing a 10x difference st=
ill caught me off guard.

Does anybody have some pointers to understand where those gains are coming =
from? Is it the CPU, memory, or both? And in particular, why does Sort bene=
fit so massively from the advancement here (~10x), but Seq Scan, Unique and=
 HashAggregate don't benefit as much (~2x)?

As you can probably tell, my hardware knowledge is very superficial, so I a=
pologize if this is a stupid question. But I'd genuinely like to improve my=
 understanding and intuition about these things.

Cheers
Felix Geisend=C3=B6rfer

[1] http://www.everymac.com/systems/apple/macbook_pro/specs/macbook-pro-cor=
e-i7-2.7-15-mid-2012-retina-display-specs.html
[2] http://www.everymac.com/systems/apple/macbook_pro/specs/macbook-pro-cor=
e-i7-2.6-15-late-2016-retina-display-touch-bar-specs.html
[3] https://explain.depesz.com/s/hmn
[4] 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