Re: [PERFORM] 10x faster sort performance on Skylake CPU vs Ivy Bridge

[Felix Geisendörfer]

> On Aug 25, 2017, at 17:07, Tom Lane  wrote:
> 
> =?utf-8?Q?Felix_Geisend=C3=B6rfer?=  writes:
>> I recently came across a performance difference between two machines that 
>> surprised me:
>> ...
>> As you can see, Machine A spends 5889ms on the Sort Node vs 609ms on Machine 
>> 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).
> 
> 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=en_US.UTF-8)
Machine B was using strcmp() (lc_collate=C)

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

CTE Scan on zulu  (cost=40673.620..40742.300 rows=3434 width=56) (actual 
time=1368.610..1368.698 rows=58 loops=1)
  CTE zulu
  ->  HashAggregate  (cost=40639.280..40673.620 rows=3434 width=56) (actual 
time=1368.607..1368.659 rows=58 loops=1)
  Group Key: mike.two, ((mike.golf)::text)
->  Unique  (cost=37656.690..40038.310 rows=34341 width=104) 
(actual time=958.493..1168.128 rows=298104 loops=1)
  ->  Sort  (cost=37656.690..38450.560 rows=317549 width=104) 
(actual time=958.491..1055.635 rows=316982 loops=1)
  Sort Key: mike.two, ((mike.lima)::text) COLLATE 
"papa", mike.echo DESC, mike.quebec
  Sort Method: quicksort  Memory: 56834kB
->  Seq Scan on mike  (cost=0.000..8638.080 rows=317549 
width=104) (actual time=0.043..172.496 rows=316982 loops=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 confirm 
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  wrote:
> 
> On Fri, Aug 25, 2017 at 8:07 AM, Tom Lane  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() :-(
> 
> strcoll() is very noticeably slower on macOS, too.
> 

Thanks. This immediately explains what I saw when testing this query on a Linux 
machine that was also using lc_collate=en_US.UTF-8 but not being slowed down by 
it as much as the macOS machine.

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

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

Re: [PERFORM] 10x faster sort performance on Skylake CPU vs Ivy Bridge

[Peter Geoghegan]

On Fri, Aug 25, 2017 at 8:07 AM, Tom Lane  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() :-(

strcoll() is very noticeably slower on macOS, too.

-- 
Peter Geoghegan


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

Re: [PERFORM] 10x faster sort performance on Skylake CPU vs Ivy Bridge

[Tom Lane]

=?utf-8?Q?Felix_Geisend=C3=B6rfer?=  writes:
> I recently came across a performance difference between two machines that 
> surprised me:
> ...
> As you can see, Machine A spends 5889ms on the Sort Node vs 609ms on Machine 
> 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).

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() :-(

regards, tom lane


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

[PERFORM] 10x faster sort performance on Skylake CPU vs Ivy Bridge

[Felix Geisendörfer]

Hi,

I recently came across a performance difference between two machines that 
surprised 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 L3 
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=40673.620..40742.300 rows=3434 width=56) (actual 
time=6339.404..6339.462 rows=58 loops=1)
  CTE zulu
  ->  HashAggregate  (cost=40639.280..40673.620 rows=3434 width=31) (actual 
time=6339.400..6339.434 rows=58 loops=1)
  Group Key: mike.two, mike.golf
->  Unique  (cost=37656.690..40038.310 rows=34341 width=64) (actual 
time=5937.934..6143.161 rows=298104 loops=1)
  ->  Sort  (cost=37656.690..38450.560 rows=317549 width=64) 
(actual time=5937.933..6031.925 rows=316982 loops=1)
  Sort Key: mike.two, mike.lima, mike.echo DESC, 
mike.quebec
  Sort Method: quicksort  Memory: 56834kB
->  Seq Scan on mike  (cost=0.000..8638.080 rows=317549 
width=64) (actual time=0.019..142.831 rows=316982 loops=1)
Filter: (golf five NOT NULL)
Rows Removed by Filter: 26426

Query Performance on Machine B: [4]

CTE Scan on zulu  (cost=40621.420..40690.100 rows=3434 width=56) (actual 
time=853.436..853.472 rows=58 loops=1)
  CTE zulu
  ->  HashAggregate  (cost=40587.080..40621.420 rows=3434 width=31) (actual 
time=853.433..853.448 rows=58 loops=1)
  Group Key: mike.two, mike.golf
->  Unique  (cost=37608.180..39986.110 rows=34341 width=64) (actual 
time=634.412..761.678 rows=298104 loops=1)
  ->  Sort  (cost=37608.180..38400.830 rows=317057 width=64) 
(actual time=634.411..694.719 rows=316982 loops=1)
  Sort Key: mike.two, mike.lima, mike.echo DESC, 
mike.quebec
  Sort Method: quicksort  Memory: 56834kB
->  Seq Scan on mike  (cost=0.000..8638.080 rows=317057 
width=64) (actual time=0.047..85.534 rows=316982 loops=1)
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 Machine 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 still 
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 benefit 
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 
apologize if this is a stupid question. But I'd genuinely like to improve my 
understanding and intuition about these things.

Cheers
Felix Geisendörfer

[1] 
http://www.everymac.com/systems/apple/macbook_pro/specs/macbook-pro-core-i7-2.7-15-mid-2012-retina-display-specs.html
[2] 
http://www.everymac.com/systems/apple/macbook_pro/specs/macbook-pro-core-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

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