Subject: Re: Why HDD performance is better than SSD in this case
To: Benjamin Scherrey <scherrey@proteus-tech.com>, Neto pr <netopr9@gmail.com>
Cc: "pgsql-performa." <pgsql-performance@postgresql.org>
References: 
 <CA+wPC0PsWacZpNX2OfRSSauUg9iNa7tReJU0oTLnM7K8PFFt6A@mail.gmail.com>
 <CACo3ShjLPE+q7xk1xcyZjEoUy5kj8Lj-wJSc8VRNQyHMEojyGQ@mail.gmail.com>
From: winston cheung <winston_cheung@163.com>
Message-ID: <96297cfc-eec0-b5ac-ef30-2fc360c52bd1@163.com>
Date: Tue, 17 Jul 2018 13:43:52 +0800
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101
 Thunderbird/52.7.0
MIME-Version: 1.0
In-Reply-To: 
 <CACo3ShjLPE+q7xk1xcyZjEoUy5kj8Lj-wJSc8VRNQyHMEojyGQ@mail.gmail.com>
Content-Type: multipart/alternative;
 boundary="------------3A844C4607E53275F9074E8E"
Content-Language: en-US
Precedence: bulk

This is a multi-part message in MIME format.
--------------3A844C4607E53275F9074E8E
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit

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


On 2018年07月17日 13:17, Benjamin Scherrey wrote:
> 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 
> <mailto: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 />


--------------3A844C4607E53275F9074E8E
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
  </head>
  <body text="#000000" bgcolor="#FFFFFF">
    <p><span id="result_box" class="" lang="en"><span class="">Can you
          show the configuration of postgresql.conf?</span><br>
        <span class="">Query configuration method:</span><br>
        <span class="">Select name, setting from pg_settings where name
          ~ 'buffers|cpu|^enable';</span></span></p>
    <br>
    <div class="moz-cite-prefix">On 2018年07月17日 13:17, Benjamin Scherrey
      wrote:<br>
    </div>
    <blockquote type="cite"
cite="mid:CACo3ShjLPE+q7xk1xcyZjEoUy5kj8Lj-wJSc8VRNQyHMEojyGQ@mail.gmail.com">
      <div dir="auto">
        <div>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. </div>
        <div dir="auto"><br>
        </div>
        <div dir="auto">  - - Ben Scherrey <br>
          <br>
          <div class="gmail_quote" dir="auto">
            <div dir="ltr">On Tue, Jul 17, 2018, 12:01 PM Neto pr &lt;<a
                href="mailto:netopr9@gmail.com" moz-do-not-send="true">netopr9@gmail.com</a>&gt;
              wrote:<br>
            </div>
            <blockquote class="gmail_quote" style="margin:0 0 0
              .8ex;border-left:1px #ccc solid;padding-left:1ex">Dear,<br>
              Some of you can help me understand this.<br>
              <br>
              This query plan is executed in the query below (query 9 of
              TPC-H<br>
              Benchmark, with scale 40, database with approximately 40
              gb).<br>
              <br>
              The experiment consisted of running the query on a HDD
              (Raid zero).<br>
              Then the same query is executed on an SSD (Raid Zero).<br>
              <br>
              Why did the HDD (7200 rpm)  perform better?<br>
              HDD - TIME 9 MINUTES<br>
              SSD - TIME 15 MINUTES<br>
              <br>
              As far as I know, the SSD has a reading that is 300 times
              faster than SSD.<br>
              <br>
              --- Execution  Plans---<br>
              ssd 40g<br>
              <a href="https://explain.depesz.com/s/rHkh"
                rel="noreferrer noreferrer" target="_blank"
                moz-do-not-send="true">https://explain.depesz.com/s/rHkh</a><br>
              <br>
              hdd 40g<br>
              <a href="https://explain.depesz.com/s/l4sq"
                rel="noreferrer noreferrer" target="_blank"
                moz-do-not-send="true">https://explain.depesz.com/s/l4sq</a><br>
              <br>
              Query ------------------------------------<br>
              <br>
              select<br>
                  nation,<br>
                  o_year,<br>
                  sum(amount) as sum_profit<br>
              from<br>
                  (<br>
                      select<br>
                          n_name as nation,<br>
                          extract(year from o_orderdate) as o_year,<br>
                          l_extendedprice * (1 - l_discount) -
              ps_supplycost *<br>
              l_quantity as amount<br>
                      from<br>
                          part,<br>
                          supplier,<br>
                          lineitem,<br>
                          partsupp,<br>
                          orders,<br>
                          nation<br>
                      where<br>
                          s_suppkey = l_suppkey<br>
                          and ps_suppkey = l_suppkey<br>
                          and ps_partkey = l_partkey<br>
                          and p_partkey = l_partkey<br>
                          and o_orderkey = l_orderkey<br>
                          and s_nationkey = n_nationkey<br>
                          and p_name like '%orchid%'<br>
                  ) as profit<br>
              group by<br>
                  nation,<br>
                  o_year<br>
              order by<br>
                  nation,<br>
                  o_year desc<br>
              <br>
            </blockquote>
          </div>
        </div>
      </div>
    </blockquote>
    <br>
    <pre class="moz-signature" cols="72">-- 
&lt;b&gt;张文升&lt;/b&gt; winston&lt;br /&gt;
PostgreSQL DBA&lt;br /&gt;</pre>
  </body>
</html>

--------------3A844C4607E53275F9074E8E--