Date: Sun, 14 Jan 2018 15:40:48 -0600
From: Justin Pryzby <pryzby@telsasoft.com>
To: Neto pr <netopr9@gmail.com>
Cc: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: HDD vs SSD without explanation
Message-ID: <20180114214048.GB32403@telsasoft.com>
References: 
 <CA+wPC0MR7Z0oiOvnenS1TGdU2gOcbSoGMW177jqPJ08DJ=DrCg@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CA+wPC0MR7Z0oiOvnenS1TGdU2gOcbSoGMW177jqPJ08DJ=DrCg@mail.gmail.com>
User-Agent: Mutt/1.5.23 (2014-03-12)
Precedence: bulk

On Sun, Jan 14, 2018 at 12:44:00PM -0800, Neto pr wrote:
> Dear all
> 
> Someone help me analyze the two execution plans below (Explain ANALYZE
> used), is the  query 9 of TPC-H benchmark [1].
>
> I'm using a server HP Intel Xeon 2.8GHz/4-core - Memory 8GB HDD SAS 320GB
> 15 Krpm AND SSD Sansung EVO 500GB.
>
> I think maybe the execution plan is using more write operations, and so the
> HDD SAS 15Krpm has been faster.

The query plan is all garbled by mail , could you resend?  Or post a link from
https://explain.depesz.com/

To see if the query is causing many writes (due to dirty pages, sorts, etc),
run with explain(analyze,buffers) 

But from what I could tell, your problems are here:

->  Parallel Seq Scan on lineitem  (cost=0.00..5861332.93 rows=100005093 width=41) (actual TIME=3.494..842667.110 rows=80004097 loops=3)
vs
->  Parallel Seq Scan on lineitem  (cost=0.00..5861333.40 rows=100005140 width=41) (actual TIME=41.805..224438.909 rows=80004097 loops=3)

->  Seq Scan on partsupp (cost=0.00..1052983.08 rows=31999708 width=22) (actual TIME=0.033..228828.149 rows=32000000 loops=3)
vs
->  Seq Scan on partsupp  (cost=0.00..1052934.38 rows=31994838 width=22) (actual TIME=0.037..37865.003 rows=32000000 loops=3)            

Can you reproduce the speed difference using dd ?
time sudo dd if=/dev/sdX of=/dev/null bs=1M count=32K skip=$((128*$RANDOM/32)) # set bs to optimal_io_size 

Or: bonnie++ -f -n0

What OS/kernel are you using?  LVM?  filesystem?  I/O scheduler?  partitions?
readahead?  blockdev --getra

If you're running under linux, maybe you can just send the output of:
for a in /sys/block/sdX/queue/*; do echo "$a `cat $a`"; done
or: tail /sys/block/sdX/queue/{minimum_io_size,optimal_io_size,read_ahead_kb,scheduler,rotational,max_sectors_kb,logical_block_size,physical_block_size}

Justin