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

Can you show the configuration of postgresql.conf?
Query configuration method:
Select name, setting from pg_settings where name ~ 'buffers|cpu|^enable';

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@gmail.com> 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

-- 
<b>张文升</b> winston<br />
PostgreSQL DBA<br />