Re: Why HDD performance is better than SSD in this case

As already mentioned by Robert, please let us know if you made sure that
nothing was fished from RAM, over the faster test.

In other words, make sure that all caches are dropped between one test
and another.

Also,to better picture the situation, would be good to know:

- which SSD (brand/model) are you using?
- which HDD?
- how are the disks configured? RAID? or not?
- on which OS?
- what are the mount options? SSD requires tuning
- did you make sure that no other query was running at the time of the
bench?
- are you making a comparison on the same machine?
- is it HW or VM? benchs should better run on bare metal to avoid
results pollution (eg: other VMS on the same hypervisor using the disk,
host caching and so on)
- how many times did you run the tests?
- did you change postgres configuration over tests?
- can you post postgres config?
- what about vacuums or maintenance tasks running in the background?

Also, to benchmark disks i would not use a custom query but pgbench.

Be aware: running benchmarks is a science, therefore needs a scientific
approach :)

regards

fabio pardi

On 07/17/2018 07:00 AM, Neto pr wrote:
> Dear,
> Some of you can help me understand this.
>
> This query plan is executed in the query below (query 9 of TPC-H
> Benchmark, with scale 40, database with approximately 40 gb).
>
> The experiment consisted of running the query on a HDD (Raid zero).
> Then the same query is executed on an SSD (Raid Zero).
>
> Why did the HDD (7200 rpm) perform better?
> HDD - TIME 9 MINUTES
> SSD - TIME 15 MINUTES
>
> As far as I know, the SSD has a reading that is 300 times faster than SSD.
>
> --- Execution Plans---
> ssd 40g
> https://explain.depesz.com/s/rHkh
>
> hdd 40g
> https://explain.depesz.com/s/l4sq
>
> Query ------------------------------------
>
> select
> nation,
> o_year,
> sum(amount) as sum_profit
> from
> (
> select
> n_name as nation,
> extract(year from o_orderdate) as o_year,
> l_extendedprice * (1 - l_discount) - ps_supplycost *
> l_quantity as amount
> from
> part,
> supplier,
> lineitem,
> partsupp,
> orders,
> nation
> where
> s_suppkey = l_suppkey
> and ps_suppkey = l_suppkey
> and ps_partkey = l_partkey
> and p_partkey = l_partkey
> and o_orderkey = l_orderkey
> and s_nationkey = n_nationkey
> and p_name like '%orchid%'
> ) as profit
> group by
> nation,
> o_year
> order by
> nation,
> o_year desc
>