From: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
To: Neto pr <netopr9@gmail.com>
To: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: Why HDD performance is better than SSD in this case
Date: Tue, 17 Jul 2018 18:07:44 +1200
Message-ID: <4fc347b1-a7f9-c0cf-58e6-99f0c37028b2@catalyst.net.nz> (raw)
In-Reply-To: <CA+wPC0PsWacZpNX2OfRSSauUg9iNa7tReJU0oTLnM7K8PFFt6A@mail.gmail.com>
References: <CA+wPC0PsWacZpNX2OfRSSauUg9iNa7tReJU0oTLnM7K8PFFt6A@mail.gmail.com>
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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mark.kirkwood@catalyst.net.nz, netopr9@gmail.com
Subject: Re: Why HDD performance is better than SSD in this case
In-Reply-To: <4fc347b1-a7f9-c0cf-58e6-99f0c37028b2@catalyst.net.nz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox