What's the on disk cache size for each drive? The better HDD performance problem won't be sustained with large amounts of data and several different queries.
- - Ben Scherrey
On Tue, Jul 17, 2018, 12:01 PM Neto pr <netopr9@xxxxxxxxx> 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
