I bet this is a ssd partition alignment problem there are erase block size of 3mb and this should be taken in account, when You partition ssd drive, creating a raid and filesystem etc...
On Mon, Jan 15, 2018 at 7:38 AM, Neto pr <netoprbr9@xxxxxxxxx> wrote:
> Hello all,
> Someone help me analyze the two execution plans below (Explain ANALYZE
> used), is the query 9 of TPC-H benchmark [1].
> I'm using two servers HP Intel Xeon 2.8GHz/4-core - Memory 8GB. O.S.
> Debian8, using EXT4 filesystem.
>
> Server 1
> - HDD SAS 15 Krpm - 320 GB (Location where O.S. Debian and Postgresql are
> installed).
>
> Server 2
> - Samsung Evo SSD 500 GB (Location where Postgresql is Installed)
> - HDD Sata 7500 Krpm - 1TB (Location where O.S Debian is installed)
>
> My DBMS parameters presents in postgresql.conf is default, but in SSD I have
> changed random_page_cost = 1.0.
>
> I do not understand, because running on an HDD SAS a query used half the
> time. I explain better, in HDD spends on average 12 minutes the query
> execution and on SSD spent 26 minutes.
> I think maybe the execution plan is using more write operations, and so the
> HDD SAS 15Krpm has been faster.
> I checked that the temporary tablespace pg_default is on the SSD in server
> 2, because when running show temp_tablespaces in psql returns empty, will be
> in the default directory, where I installed the DBMS in:
> /media/ssd500gb/opt/pgv101norssd/data.
>
> Anyway, I always thought that an SSD would be equal or faster, but in the
> case and four more cases we have here, it lost a lot for the HDDs.
Generally for reading data, yes, but you changed the query plan also.
To get to the bottom of this let's get SSD performance numbers for
both plans and HDD performance numbers for both plans. You're trying
to measure device performance about are probably measuring the
relative efficiencies of the generated plans.
