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.
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 SAS 15K --------------------------------------
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 (ANALYZE, BUFFERS) on SSD Storage--------------------------------------------------
Finalize GroupAggregate (cost=15822228.33..15980046.69 rows=60150 width=66) (actual time=1569793.025..1573969.614 rows=175 loops=1) Group Key: nation.n_name, (date_part(_year_::text, (orders.o_orderdate)::timestamp without time zone)) Buffers: shared hit=1237677 read=2399403, temp read=1186697 written=1183781 -> Gather Merge (cost=15822228.33..15977791.06 rows=120300 width=66) (actual time=1569767.662..1573968.933 rows=525 loops=1) Workers Planned: 2 Workers Launched: 2 Buffers: shared hit=1237677 read=2399403, temp read=1186697 written=1183781 -> Partial GroupAggregate (cost=15821228.31..15962905.44 rows=60150 width=66) (actual time=1547834.941..1552040.073 rows=175 loops=3) Group Key: nation.n_name, (date_part(_year_::text, (orders.o_orderdate)::timestamp without time zone)) Buffers: shared hit=3522992 read=7371656, temp read=3551003 written=3542253 -> Sort (cost=15821228.31..15838806.37 rows=7031225 width=57) (actual time=1547819.849..1548887.629 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: 321648kB Buffers: shared hit=3522992 read=7371656, temp read=3551003 written=3542253 -> Hash Join (cost=4708859.28..14719466.13 rows=7031225 width=57) (actual time=1220169.593..1541279.300 rows=4344197 loops=3) Hash Cond: (lineitem.l_suppkey = supplier.s_suppkey) Buffers: shared hit=3522922 read=7371656, temp read=3220661 written=3211373 -> Hash Join (cost=4683017.71..14434606.65 rows=7071075 width=43) (actual time=1142575.564..1535092.395 rows=4344197 loops=3) Hash Cond: (lineitem.l_orderkey = orders.o_orderkey) Buffers: shared hit=3503999 read=7362903, temp read=3114233 written=3104987 -> Hash Join (cost=1993687.71..11297331.33 rows=7071075 width=47) (actual time=275104.573..1213552.106 rows=4344197 loops=3) Hash Cond: ((lineitem.l_suppkey = partsupp.ps_suppkey) AND (lineitem.l_partkey = partsupp.ps_partkey)) Buffers: shared hit=1478115 read=6073916, temp read=2369833 written=2366725 -> Hash Join (cost=273201.71..9157213.44 rows=7071075 width=45) (actual time=24569.390..895992.716 rows=4344197 loops=3) Hash Cond: (lineitem.l_partkey = part.p_partkey) Buffers: shared hit=314284 read=5038767, temp read=1742656 written=1742614 -> Parallel Seq Scan on lineitem (cost=0.00..5861333.20 rows=100005120 width=41) (actual time=0.147..712469.002 rows=80004097 loops=3) Buffers: shared hit=482 read=4860800 -> Hash (cost=263921.00..263921.00 rows=565657 width=4) (actual time=24556.402..24556.402 rows=434469 loops=3) Buckets: 131072 Batches: 8 Memory Usage: 2933kB Buffers: shared hit=313796 read=177967, temp written=3327 -> Seq Scan on part (cost=0.00..263921.00 rows=565657 width=4) (actual time=0.073..24418.923 rows=434469 loops=3) Filter: ((p_name)::text ~~ _%orchid%_::text) Rows Removed by Filter: 7565531 Buffers: shared hit=313796 read=177967 -> Hash (cost=1052986.00..1052986.00 rows=32000000 width=22) (actual time=250328.161..250328.161 rows=32000000 loops=3) Buckets: 65536 Batches: 512 Memory Usage: 3941kB Buffers: shared hit=1163809 read=1035149, temp written=513846 -> Seq Scan on partsupp (cost=0.00..1052986.00 rows=32000000 width=22) (actual time=0.042..238352.960 rows=32000000 loops=3) Buffers: shared hit=1163809 read=1035149 -> Hash (cost=1704955.00..1704955.00 rows=60000000 width=8) (actual time=272705.587..272705.587 rows=60000000 loops=3) Buckets: 131072 Batches: 1024 Memory Usage: 3316kB Buffers: shared hit=2025878 read=1288987, temp written=613128 -> Seq Scan on orders (cost=0.00..1704955.00 rows=60000000 width=8) (actual time=0.149..256480.758 rows=60000000 loops=3) Buffers: shared hit=2025878 read=1288987 -> Hash (cost=18106.56..18106.56 rows=400000 width=30) (actual time=597.929..597.929 rows=400000 loops=3) Buckets: 65536 Batches: 8 Memory Usage: 3549kB Buffers: shared hit=18841 read=8753, temp written=6396 -> Hash Join (cost=1.56..18106.56 rows=400000 width=30) (actual time=0.269..518.588 rows=400000 loops=3) Hash Cond: (supplier.s_nationkey = nation.n_nationkey) Buffers: shared hit=18841 read=8753 -> Seq Scan on supplier (cost=0.00..13197.00 rows=400000 width=12) (actual time=0.246..435.109 rows=400000 loops=3) Buffers: shared hit=18838 read=8753 -> Hash (cost=1.25..1.25 rows=25 width=30) (actual time=0.016..0.016 rows=25 loops=3) Buckets: 1024 Batches: 1 Memory Usage: 10kB Buffers: shared hit=3 -> Seq Scan on nation (cost=0.00..1.25 rows=25 width=30) (actual time=0.007..0.010 rows=25 loops=3) Buffers: shared hit=3Planning time: 2.319 msExecution time: 1574019.504 ms
------------------Execution plan (Explain Analyze) 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
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.
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 SAS 15K --------------------------------------
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 (ANALYZE, BUFFERS) on SSD Storage--------------------------------------------------
Finalize GroupAggregate (cost=15822228.33..15980046.69 rows=60150 width=66) (actual time=1569793.025..1573969.614 rows=175 loops=1) Group Key: nation.n_name, (date_part(_year_::text, (orders.o_orderdate)::timestamp without time zone)) Buffers: shared hit=1237677 read=2399403, temp read=1186697 written=1183781 -> Gather Merge (cost=15822228.33..15977791.06 rows=120300 width=66) (actual time=1569767.662..1573968.933 rows=525 loops=1) Workers Planned: 2 Workers Launched: 2 Buffers: shared hit=1237677 read=2399403, temp read=1186697 written=1183781 -> Partial GroupAggregate (cost=15821228.31..15962905.44 rows=60150 width=66) (actual time=1547834.941..1552040.073 rows=175 loops=3) Group Key: nation.n_name, (date_part(_year_::text, (orders.o_orderdate)::timestamp without time zone)) Buffers: shared hit=3522992 read=7371656, temp read=3551003 written=3542253 -> Sort (cost=15821228.31..15838806.37 rows=7031225 width=57) (actual time=1547819.849..1548887.629 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: 321648kB Buffers: shared hit=3522992 read=7371656, temp read=3551003 written=3542253 -> Hash Join (cost=4708859.28..14719466.13 rows=7031225 width=57) (actual time=1220169.593..1541279.300 rows=4344197 loops=3) Hash Cond: (lineitem.l_suppkey = supplier.s_suppkey) Buffers: shared hit=3522922 read=7371656, temp read=3220661 written=3211373 -> Hash Join (cost=4683017.71..14434606.65 rows=7071075 width=43) (actual time=1142575.564..1535092.395 rows=4344197 loops=3) Hash Cond: (lineitem.l_orderkey = orders.o_orderkey) Buffers: shared hit=3503999 read=7362903, temp read=3114233 written=3104987 -> Hash Join (cost=1993687.71..11297331.33 rows=7071075 width=47) (actual time=275104.573..1213552.106 rows=4344197 loops=3) Hash Cond: ((lineitem.l_suppkey = partsupp.ps_suppkey) AND (lineitem.l_partkey = partsupp.ps_partkey)) Buffers: shared hit=1478115 read=6073916, temp read=2369833 written=2366725 -> Hash Join (cost=273201.71..9157213.44 rows=7071075 width=45) (actual time=24569.390..895992.716 rows=4344197 loops=3) Hash Cond: (lineitem.l_partkey = part.p_partkey) Buffers: shared hit=314284 read=5038767, temp read=1742656 written=1742614 -> Parallel Seq Scan on lineitem (cost=0.00..5861333.20 rows=100005120 width=41) (actual time=0.147..712469.002 rows=80004097 loops=3) Buffers: shared hit=482 read=4860800 -> Hash (cost=263921.00..263921.00 rows=565657 width=4) (actual time=24556.402..24556.402 rows=434469 loops=3) Buckets: 131072 Batches: 8 Memory Usage: 2933kB Buffers: shared hit=313796 read=177967, temp written=3327 -> Seq Scan on part (cost=0.00..263921.00 rows=565657 width=4) (actual time=0.073..24418.923 rows=434469 loops=3) Filter: ((p_name)::text ~~ _%orchid%_::text) Rows Removed by Filter: 7565531 Buffers: shared hit=313796 read=177967 -> Hash (cost=1052986.00..1052986.00 rows=32000000 width=22) (actual time=250328.161..250328.161 rows=32000000 loops=3) Buckets: 65536 Batches: 512 Memory Usage: 3941kB Buffers: shared hit=1163809 read=1035149, temp written=513846 -> Seq Scan on partsupp (cost=0.00..1052986.00 rows=32000000 width=22) (actual time=0.042..238352.960 rows=32000000 loops=3) Buffers: shared hit=1163809 read=1035149 -> Hash (cost=1704955.00..1704955.00 rows=60000000 width=8) (actual time=272705.587..272705.587 rows=60000000 loops=3) Buckets: 131072 Batches: 1024 Memory Usage: 3316kB Buffers: shared hit=2025878 read=1288987, temp written=613128 -> Seq Scan on orders (cost=0.00..1704955.00 rows=60000000 width=8) (actual time=0.149..256480.758 rows=60000000 loops=3) Buffers: shared hit=2025878 read=1288987 -> Hash (cost=18106.56..18106.56 rows=400000 width=30) (actual time=597.929..597.929 rows=400000 loops=3) Buckets: 65536 Batches: 8 Memory Usage: 3549kB Buffers: shared hit=18841 read=8753, temp written=6396 -> Hash Join (cost=1.56..18106.56 rows=400000 width=30) (actual time=0.269..518.588 rows=400000 loops=3) Hash Cond: (supplier.s_nationkey = nation.n_nationkey) Buffers: shared hit=18841 read=8753 -> Seq Scan on supplier (cost=0.00..13197.00 rows=400000 width=12) (actual time=0.246..435.109 rows=400000 loops=3) Buffers: shared hit=18838 read=8753 -> Hash (cost=1.25..1.25 rows=25 width=30) (actual time=0.016..0.016 rows=25 loops=3) Buckets: 1024 Batches: 1 Memory Usage: 10kB Buffers: shared hit=3 -> Seq Scan on nation (cost=0.00..1.25 rows=25 width=30) (actual time=0.007..0.010 rows=25 loops=3) Buffers: shared hit=3Planning time: 2.319 msExecution time: 1574019.504 ms
------------------Execution plan (Explain Analyze) 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
