Re: HDD vs SSD without explanation - Mailing list pgsql-performance

Thanks for the reply.
I'll try upload the execution plan with Explain (analyse, buffer) for
website:  https://explain.depesz.com/

I'm make an experiment for a scientific research and this is what I
find strange, explaining better, strange HDD performance far outweigh
the performance of an SSD.

Do you think that if you run a VACUMM FULL the performance with the
SSD will be better than a 15Krpm SAS HDD?

Best Regards
Neto
<div id="DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2"><br /> <table
style="border-top: 1px solid #D3D4DE;">
    <tr>
      <td style="width: 55px; padding-top: 18px;"><a
href="https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail"
target="_blank"><img
src="https://ipmcdn.avast.com/images/icons/icon-envelope-tick-round-orange-animated-no-repeat-v1.gif"
alt="" width="46" height="29" style="width: 46px; height: 29px;"
/></a></td>
        <td style="width: 470px; padding-top: 17px; color: #41424e;
font-size: 13px; font-family: Arial, Helvetica, sans-serif;
line-height: 18px;">Livre de vírus. <a
href="https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail"
target="_blank" style="color: #4453ea;">www.avast.com</a>.         </td>
    </tr>
</table>
<a href="#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2" width="1" height="1"></a></div>

2018-01-14 19:40 GMT-02:00 Justin Pryzby <pryzby@telsasoft.com>:
> 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=80004097loops=3) 
> vs
> ->  Parallel Seq Scan on lineitem  (cost=0.00..5861333.40 rows=100005140 width=41) (actual TIME=41.805..224438.909
rows=80004097loops=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