Sorry.. I replied in the wrong message before ... follows my response. ------------- Thanks all, but I still have not figured it out. This is really strange because the tests were done on the same machine (I use HP ML110 Proliant 8gb RAM - Xeon 2.8 ghz processor (4 cores), and POSTGRESQL 10.1. - Only the mentioned query running at the time of the test. - I repeated the query 7 times and did not change the results. - Before running each batch of 7 executions, I discarded the Operating System cache and restarted DBMS like this: (echo 3> / proc / sys / vm / drop_caches; discs: - 2 units of Samsung Evo SSD 500 GB (mounted on ZERO RAID) - 2 SATA 7500 Krpm HDD units - 1TB (mounted on ZERO RAID) - The Operating System and the Postgresql DBMS are installed on the SSD disk. Best Regards [ ]`s Neto 2018-07-17 1:08 GMT-07:00 Fabio Pardi <f.pardi@xxxxxxxxxxxx>: > As already mentioned by Robert, please let us know if you made sure that > nothing was fished from RAM, over the faster test. > > In other words, make sure that all caches are dropped between one test > and another. > > Also,to better picture the situation, would be good to know: > > - which SSD (brand/model) are you using? > - which HDD? > - how are the disks configured? RAID? or not? > - on which OS? > - what are the mount options? SSD requires tuning > - did you make sure that no other query was running at the time of the > bench? > - are you making a comparison on the same machine? > - is it HW or VM? benchs should better run on bare metal to avoid > results pollution (eg: other VMS on the same hypervisor using the disk, > host caching and so on) > - how many times did you run the tests? > - did you change postgres configuration over tests? > - can you post postgres config? > - what about vacuums or maintenance tasks running in the background? > > Also, to benchmark disks i would not use a custom query but pgbench. > > Be aware: running benchmarks is a science, therefore needs a scientific > approach :) > > regards > > fabio pardi > > > > On 07/17/2018 07:00 AM, 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 >> >
