On Tue, Jul 17, 2018 at 1:00 AM, 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.
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
