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

Can you post make and model of the SSD concerned? In general the cheaper 
consumer grade ones cannot do sustained read/writes at anything like 
their quoted max values.

regards

Mark


On 17/07/18 17:00, 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
>