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

On Tue, Jul 17, 2018 at 1:00 AM, Neto pr <netopr9(at)gmail(dot)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.
>

Is the 300 times faster comparing random to random, or sequential to
sequential? Maybe your SSD simply fails to perform as advertised. This
would not surprise me at all.

To remove some confounding variables, can you turn off parallelism and
repeat the queries? (Yes, they will probably get slower. But is the
relative timings still the same?) Also, turn on track_io_timings and
repeat the "EXPLAIN (ANALYZE, BUFFERS)", perhaps with TIMINGS OFF.

Also, see how long it takes to read the entire database, or just the
largest table, outside of postgres.

Something like:

time tar -f - $PGDATA/base | wc -c

or

time cat $PGDATA/base/<database oid>/<large table file node>* | wc -c

Cheers,

Jeff