2018-01-14 15:59 GMT-08:00 Neto pr <netopr9@xxxxxxxxx>: > Thanks for the reply. > I'll try upload the execution plan with Explain (analyse, buffer) for > website: https://explain.depesz.com/ > Below is a new execution plan, with Analyze, BUFFERS. This time, without changing anything in the configuration of the DBMS, I just rebooted the DBMS, the time of 16 minutes was obtained, against the 26 minutes of another execution. But it still has not managed to exceed the execution time in HDD SAS 15Krpm. I was not able to upload to the site, because I'm saving the execution plan in the database, and when I retrieve it, it loses the line breaks, and the dxxxx site does not allow uploading. ------------------- Execution Plan with Buffers executed on SSD Stores.--------------------------------------------------------------------------------------------- Finalize GroupAggregate (cost=15822228.33..15980046.69 rows=60150 width=66) (actual time=969248.287..973686.679 rows=175 loops=1) Group Key: nation.n_name, (date_part(_year_::text, (orders.o_orderdate)::timestamp without time zone)) Buffers: shared hit=1327602 read=2305013, temp read=1183857 written=1180940 -> Gather Merge (cost=15822228.33..15977791.06 rows=120300 width=66) (actual time=969222.164..973685.582 rows=525 loops=1) Workers Planned: 2 Workers Launched: 2 Buffers: shared hit=1327602 read=2305013, temp read=1183857 written=1180940 -> Partial GroupAggregate (cost=15821228.31..15962905.44 rows=60150 width=66) (actual time=941985.137..946403.344 rows=175 loops=3) Group Key: nation.n_name, (date_part(_year_::text, (orders.o_orderdate)::timestamp without time zone)) Buffers: shared hit=3773802 read=7120852, temp read=3550293 written=3541542 -> Sort (cost=15821228.31..15838806.37 rows=7031225 width=57) (actual time=941954.595..943119.850 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: 320784kB Buffers: shared hit=3773802 read=7120852, temp read=3550293 written=3541542 -> Hash Join (cost=4708859.28..14719466.13 rows=7031225 width=57) (actual time=619996.638..933725.615 rows=4344197 loops=3) Hash Cond: (lineitem.l_suppkey = supplier.s_suppkey) Buffers: shared hit=3773732 read=7120852, temp read=3220697 written=3211409 -> Hash Join (cost=4683017.71..14434606.65 rows=7071075 width=43) (actual time=579893.395..926348.061 rows=4344197 loops=3) Hash Cond: (lineitem.l_orderkey = orders.o_orderkey) Buffers: shared hit=3758207 read=7108695, temp read=3114271 written=3105025 -> Hash Join (cost=1993687.71..11297331.33 rows=7071075 width=47) (actual time=79741.803..805259.856 rows=4344197 loops=3) Hash Cond: ((lineitem.l_suppkey = partsupp.ps_suppkey) AND (lineitem.l_partkey = partsupp.ps_partkey)) Buffers: shared hit=1754251 read=5797780, temp read=2369849 written=2366741 -> Hash Join (cost=273201.71..9157213.44 rows=7071075 width=45) (actual time=5363.078..672302.517 rows=4344197 loops=3) Hash Cond: (lineitem.l_partkey = part.p_partkey) Buffers: shared hit=325918 read=5027133, temp read=1742658 written=1742616 -> Parallel Seq Scan on lineitem (cost=0.00..5861333.20 rows=100005120 width=41) (actual time=0.129..536226.436 rows=80004097 loops=3) Buffers: shared hit=2 read=4861280 -> Hash (cost=263921.00..263921.00 rows=565657 width=4) (actual time=5362.100..5362.100 rows=434469 loops=3) Buckets: 131072 Batches: 8 Memory Usage: 2933kB Buffers: shared hit=325910 read=165853, temp written=3327 -> Seq Scan on part (cost=0.00..263921.00 rows=565657 width=4) (actual time=0.025..5279.959 rows=434469 loops=3) Filter: ((p_name)::text ~~ _%orchid%_::text) Rows Removed by Filter: 7565531 Buffers: shared hit=325910 read=165853 -> Hash (cost=1052986.00..1052986.00 rows=32000000 width=22) (actual time=74231.061..74231.061 rows=32000000 loops=3) Buckets: 65536 Batches: 512 Memory Usage: 3941kB Buffers: shared hit=1428311 read=770647, temp written=513846 -> Seq Scan on partsupp (cost=0.00..1052986.00 rows=32000000 width=22) (actual time=0.037..66316.652 rows=32000000 loops=3) Buffers: shared hit=1428311 read=770647 -> Hash (cost=1704955.00..1704955.00 rows=60000000 width=8) (actual time=46310.630..46310.630 rows=60000000 loops=3) Buckets: 131072 Batches: 1024 Memory Usage: 3316kB Buffers: shared hit=2003950 read=1310915, temp written=613128 -> Seq Scan on orders (cost=0.00..1704955.00 rows=60000000 width=8) (actual time=0.033..34352.493 rows=60000000 loops=3) Buffers: shared hit=2003950 read=1310915 -> Hash (cost=18106.56..18106.56 rows=400000 width=30) (actual time=226.360..226.360 rows=400000 loops=3) Buckets: 65536 Batches: 8 Memory Usage: 3549kB Buffers: shared hit=15437 read=12157, temp written=6396 -> Hash Join (cost=1.56..18106.56 rows=400000 width=30) (actual time=0.037..145.779 rows=400000 loops=3) Hash Cond: (supplier.s_nationkey = nation.n_nationkey) Buffers: shared hit=15437 read=12157 -> Seq Scan on supplier (cost=0.00..13197.00 rows=400000 width=12) (actual time=0.014..63.768 rows=400000 loops=3) Buffers: shared hit=15434 read=12157 -> Hash (cost=1.25..1.25 rows=25 width=30) (actual time=0.015..0.015 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.006..0.008 rows=25 loops=3) Buffers: shared hit=3Planning time: 16.668 msExecution time: 973799.430 ms ------------------------------------------------------------------------------------------------------------------------------------------------ > I'm make an experiment for a scientific research and this is what I > find strange, explaining better, strange HDD performance far outweigh > the performance of an SSD. > > Do you think that if you run a VACUMM FULL the performance with the > SSD will be better than a 15Krpm SAS HDD? > > Best Regards > Neto > <div id="DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2"><br /> <table > style="border-top: 1px solid #D3D4DE;"> > <tr> > <td style="width: 55px; padding-top: 18px;"><a > href="https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail"; > target="_blank"><img > src="https://ipmcdn.avast.com/images/icons/icon-envelope-tick-round-orange-animated-no-repeat-v1.gif"; > alt="" width="46" height="29" style="width: 46px; height: 29px;" > /></a></td> > <td style="width: 470px; padding-top: 17px; color: #41424e; > font-size: 13px; font-family: Arial, Helvetica, sans-serif; > line-height: 18px;">Livre de vírus. <a > href="https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail"; > target="_blank" style="color: #4453ea;">www.avast.com</a>. </td> > </tr> > </table> > <a href="#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2" width="1" height="1"></a></div> > > 2018-01-14 19:40 GMT-02:00 Justin Pryzby <pryzby@xxxxxxxxxxxxx>: >> On Sun, Jan 14, 2018 at 12:44:00PM -0800, Neto pr wrote: >>> 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. >>> >>> I think maybe the execution plan is using more write operations, and so the >>> HDD SAS 15Krpm has been faster. >> >> The query plan is all garbled by mail , could you resend? Or post a link from >> https://explain.depesz.com/ >> >> To see if the query is causing many writes (due to dirty pages, sorts, etc), >> run with explain(analyze,buffers) >> >> But from what I could tell, your problems are here: >> >> -> Parallel Seq Scan on lineitem (cost=0.00..5861332.93 rows=100005093 width=41) (actual TIME=3.494..842667.110 rows=80004097 loops=3) >> vs >> -> Parallel Seq Scan on lineitem (cost=0.00..5861333.40 rows=100005140 width=41) (actual TIME=41.805..224438.909 rows=80004097 loops=3) >> >> -> Seq Scan on partsupp (cost=0.00..1052983.08 rows=31999708 width=22) (actual TIME=0.033..228828.149 rows=32000000 loops=3) >> vs >> -> Seq Scan on partsupp (cost=0.00..1052934.38 rows=31994838 width=22) (actual TIME=0.037..37865.003 rows=32000000 loops=3) >> >> Can you reproduce the speed difference using dd ? >> time sudo dd if=/dev/sdX of=/dev/null bs=1M count=32K skip=$((128*$RANDOM/32)) # set bs to optimal_io_size >> >> Or: bonnie++ -f -n0 >> >> What OS/kernel are you using? LVM? filesystem? I/O scheduler? partitions? >> readahead? blockdev --getra >> >> If you're running under linux, maybe you can just send the output of: >> for a in /sys/block/sdX/queue/*; do echo "$a `cat $a`"; done >> or: tail /sys/block/sdX/queue/{minimum_io_size,optimal_io_size,read_ahead_kb,scheduler,rotational,max_sectors_kb,logical_block_size,physical_block_size} >> >> Justin
![https://ipmcdn.avast.com/images/icons/icon-envelope-tick-round-orange-animated-no-repeat-v1.gif"](https://ipmcdn.avast.com/images/icons/icon-envelope-tick-round-orange-animated-no-repeat-v1.gif"){kind=link}
