HDD vs SSD without explanation

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Dear 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 a server HP Intel Xeon 2.8GHz/4-core - Memory 8GB HDD SAS 320GB 15 Krpm AND SSD Sansung EVO 500GB.
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 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.
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.

Any help in understanding, is welcome

Best Regards
Neto

----------------- Query execution Time on SSD ---------------
execution 1: 00:23:29
execution 2: 00:28:38
execution 3: 00:27:32
execution 4: 00:27:54
execution 5: 00:27:35
execution 6: 00:26:19
Average: 26min 54 seconds

------------Query execution Time on HDD -------------------------------------------------------------------------------
execution 1: 00:12:44
execution 2: 00:12:30
execution 3: 00:12:47
execution 4: 00:13:02
execution 5: 00:13:00
execution 6: 00:12:47
Average: 12 minutes 48 seconds

---------------------------------- EXECUTION PLAN SSD Storage--------------------------------------------------------
Finalize GroupAggregate  (cost=15.694.362.41..15842178.65 rows=60150 width=66) (actual time=1670577.649..1674717.444 rows=175 loops=1)  Group Key: nation.n_name, (date_part(_year_::text, (orders.o_orderdate)::timestamp without time zone))  ->  Gather Merge  (cost=15694362.41..15839923.02 rows=120300 width=66) (actual time=1670552.446..1674716.748 rows=525 loops=1)        Workers Planned: 2        Workers Launched: 2        ->  Partial GroupAggregate  (cost=15693362.39..15825037.39 rows=60150 width=66) (actual time=1640482.164..1644619.574 rows=175 loops=3)              Group Key: nation.n_name, (date_part(_year_::text, (orders.o_orderdate)::timestamp without time zone))              ->  Sort  (cost=15693362.39..15709690.19 rows=6531119 width=57) (actual time=1640467.384..1641511.970 rows=4344197 loops=3)                    Sort Key: nation.n_name, (date_part(_year_::text, (orders.o_orderdate)::timestamp without time zone)) DESC                    Sort Method: external merge  Disk: 319512kB                    ->  Hash Join  (cost=4708869.23..14666423.78 rows=6531119 width=57) (actual time=1366753.586..1634128.122 rows=4344197 loops=3)                          Hash Cond: (lineitem.l_suppkey = supplier.s_suppkey)                          ->  Hash Join  (cost=4683027.67..14400582.74 rows=6531119 width=43) (actual time=1328019.213..1623919.675 rows=4344197 loops=3)                                Hash Cond: (lineitem.l_orderkey = orders.o_orderkey)                                ->  Hash Join  (cost=1993678.29..11279593.98 rows=6531119 width=47) (actual time=245906.330..1316201.213 rows=4344197 loops=3)                                      Hash Cond: ((lineitem.l_suppkey = partsupp.ps_suppkey) AND (lineitem.l_partkey = partsupp.ps_partkey))                                      ->  Hash Join  (cost=273200.59..9157211.71 rows=6531119 width=45) (actual time=5103.563..1007657.993 rows=4344197 loops=3)                                            Hash Cond: (lineitem.l_partkey = part.p_partkey)                                            ->  Parallel Seq Scan on lineitem  (cost=0.00..5861332.93 rows=100005093 width=41) (actual time=3.494..842667.110 rows=80004097 loops=3)                                            ->  Hash  (cost=263919.95..263919.95 rows=565651 width=4) (actual time=4973.807..4973.807 rows=434469 loops=3)                                                  Buckets: 131072  Batches: 8  Memory Usage: 2933kB                                                  ->  Seq Scan on part  (cost=0.00..263919.95 rows=565651 width=4) (actual time=11.810..4837.287 rows=434469 loops=3)                                                        Filter: ((p_name)::text ~~ _%orchid%_::text)                                                        Rows Removed by Filter: 7565531                                      ->  Hash  (cost=1052983.08..1052983.08 rows=31999708 width=22) (actual time=240711.936..240711.936 rows=32000000 loops=3)                                            Buckets: 65536  Batches: 512  Memory Usage: 3941kB                                            ->  Seq Scan on partsupp  (cost=0.00..1052983.08 rows=31999708 width=22) (actual time=0.033..228828.149 rows=32000000 loops=3)                                ->  Hash  (cost=1704962.28..1704962.28 rows=60000728 width=8) (actual time=253669.242..253669.242 rows=60000000 loops=3)                                      Buckets: 131072  Batches: 1024  Memory Usage: 3316kB                                      ->  Seq Scan on orders  (cost=0.00..1704962.28 rows=60000728 width=8) (actual time=0.038..237545.226 rows=60000000 loops=3)                          ->  Hash  (cost=18106.56..18106.56 rows=400000 width=30) (actual time=277.283..277.283 rows=400000 loops=3)                                Buckets: 65536  Batches: 8  Memory Usage: 3549kB                                ->  Hash Join  (cost=1.56..18106.56 rows=400000 width=30) (actual time=45.155..205.372 rows=400000 loops=3)                                      Hash Cond: (supplier.s_nationkey = nation.n_nationkey)                                      ->  Seq Scan on supplier  (cost=0.00..13197.00 rows=400000 width=12) (actual time=45.094..129.333 rows=400000 loops=3)                                      ->  Hash  (cost=1.25..1.25 rows=25 width=30) (actual time=0.038..0.038 rows=25 loops=3)                                            Buckets: 1024  Batches: 1  Memory Usage: 10kB                                            ->  Seq Scan on nation  (cost=0.00..1.25 rows=25 width=30) (actual time=0.026..0.029 rows=25 loops=3)Planning time: 2.251 msExecution time: 1674790.954 ms


--------------------------------------------------Execution plan on HDD Storage -------------------------------------------------
Finalize GroupAggregate  (cost=14.865.093.59..14942715.87 rows=60150 width=66) (actual time=763039.932..767231.344 rows=175 loops=1)  Group Key: nation.n_name, (date_part(_year_::text, (orders.o_orderdate)::timestamp without time zone))  ->  Gather Merge  (cost=14865093.59..14940460.24 rows=120300 width=66) (actual time=763014.187..767230.826 rows=525 loops=1)        Workers Planned: 2        Workers Launched: 2        ->  Partial GroupAggregate  (cost=14864093.57..14925574.61 rows=60150 width=66) (actual time=758405.567..762576.512 rows=175 loops=3)              Group Key: nation.n_name, (date_part(_year_::text, (orders.o_orderdate)::timestamp without time zone))              ->  Sort  (cost=14864093.57..14871647.12 rows=3021421 width=57) (actual time=758348.786..759400.608 rows=4344197 loops=3)                    Sort Key: nation.n_name, (date_part(_year_::text, (orders.o_orderdate)::timestamp without time zone)) DESC                    Sort Method: external merge  Disk: 324568kB                    ->  Hash Join  (cost=4703389.12..14311687.00 rows=3021421 width=57) (actual time=474033.697..736861.120 rows=4344197 loops=3)                          Hash Cond: (lineitem.l_suppkey = supplier.s_suppkey)                          ->  Hash Join  (cost=4677547.56..14173154.89 rows=3030463 width=43) (actual time=420246.635..728731.259 rows=4344197 loops=3)                                Hash Cond: (lineitem.l_orderkey = orders.o_orderkey)                                ->  Hash Join  (cost=1988224.59..11157928.33 rows=3030463 width=47) (actual time=92246.411..545600.522 rows=4344197 loops=3)                                      Hash Cond: ((lineitem.l_suppkey = partsupp.ps_suppkey) AND (lineitem.l_partkey = partsupp.ps_partkey))                                      ->  Hash Join  (cost=267897.64..9150646.81 rows=3030463 width=45) (actual time=9247.722..368140.568 rows=4344197 loops=3)                                            Hash Cond: (lineitem.l_partkey = part.p_partkey)                                            ->  Parallel Seq Scan on lineitem  (cost=0.00..5861333.40 rows=100005140 width=41) (actual time=41.805..224438.909 rows=80004097 loops=3)                                            ->  Hash  (cost=263920.35..263920.35 rows=242423 width=4) (actual time=9181.407..9181.407 rows=434469 loops=3)                                                  Buckets: 131072 (originally 131072)  Batches: 8 (originally 4)  Memory Usage: 3073kB                                                  ->  Seq Scan on part  (cost=0.00..263920.35 rows=242423 width=4) (actual time=5.608..9027.871 rows=434469 loops=3)                                                        Filter: ((p_name)::text ~~ _%orchid%_::text)                                                        Rows Removed by Filter: 7565531                                      ->  Hash  (cost=1052934.38..1052934.38 rows=31994838 width=22) (actual time=82524.045..82524.045 rows=32000000 loops=3)                                            Buckets: 65536  Batches: 512  Memory Usage: 3941kB                                            ->  Seq Scan on partsupp  (cost=0.00..1052934.38 rows=31994838 width=22) (actual time=0.037..37865.003 rows=32000000 loops=3)                                ->  Hash  (cost=1704952.32..1704952.32 rows=59999732 width=8) (actual time=98182.919..98182.919 rows=60000000 loops=3)                                      Buckets: 131072  Batches: 1024  Memory Usage: 3316kB                                      ->  Seq Scan on orders  (cost=0.00..1704952.32 rows=59999732 width=8) (actual time=0.042..43977.490 rows=60000000 loops=3)                          ->  Hash  (cost=18106.56..18106.56 rows=400000 width=30) (actual time=555.225..555.225 rows=400000 loops=3)                                Buckets: 65536  Batches: 8  Memory Usage: 3549kB                                ->  Hash Join  (cost=1.56..18106.56 rows=400000 width=30) (actual time=1.748..484.203 rows=400000 loops=3)                                      Hash Cond: (supplier.s_nationkey = nation.n_nationkey)                                      ->  Seq Scan on supplier  (cost=0.00..13197.00 rows=400000 width=12) (actual time=1.718..408.463 rows=400000 loops=3)                                      ->  Hash  (cost=1.25..1.25 rows=25 width=30) (actual time=0.019..0.019 rows=25 loops=3)                                            Buckets: 1024  Batches: 1  Memory Usage: 10kB                                            ->  Seq Scan on nation  (cost=0.00..1.25 rows=25 width=30) (actual time=0.007..0.010 rows=25 loops=3)Planning time: 12.145 msExecution time: 767503.736 ms


-- Query SQL ------------------

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


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux