explain.depesz.com

PostgreSQL's explain analyze made readable

Result: l4sq : q9 hdd 40g rd nor NoIdx

Settings
# exclusive inclusive rows x rows loops node
1. 0.823 574,594.203 ↑ 2,337.3 175 1

Finalize GroupAggregate (cost=16,500,525.06..16,735,574.28 rows=409,020 width=144) (actual time=570,325.842..574,594.203 rows=175 loops=1)

  • Group Key: nation.n_name, (date_part(_year_::text, (orders.o_orderdate)::timestamp without time zone))
2. 1,340.934 574,593.380 ↑ 1,558.2 525 1

Gather Merge (cost=16,500,525.06..16,720,236.03 rows=818,040 width=144) (actual time=570,300.295..574,593.380 rows=525 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
3. 3,157.748 573,252.446 ↑ 2,337.3 175 3 / 3

Partial GroupAggregate (cost=16,499,525.04..16,624,813.89 rows=409,020 width=144) (actual time=569,049.201..573,252.446 rows=175 loops=3)

  • Group Key: nation.n_name, (date_part(_year_::text, (orders.o_orderdate)::timestamp without time zone))
4. 7,675.038 570,094.698 ↑ 1.4 4,344,197 3 / 3

Sort (cost=16,499,525.04..16,514,291.42 rows=5,906,550 width=135) (actual time=569,034.226..570,094.698 rows=4,344,197 loops=3)

  • Sort Key: nation.n_name, (date_part(_year_::text, (orders.o_orderdate)::timestamp without time zone)) DESC
  • Sort Method: external merge Disk: 319,952kB
5. 4,394.293 562,419.660 ↑ 1.4 4,344,197 3 / 3

Hash Join (cost=4,711,904.21..14,623,907.28 rows=5,906,550 width=135) (actual time=412,606.682..562,419.660 rows=4,344,197 loops=3)

  • Hash Cond: (lineitem.l_suppkey = supplier.s_suppkey)
6. 78,073.327 557,685.881 ↑ 1.4 4,344,197 3 / 3

Hash Join (cost=4,682,023.27..14,369,523.42 rows=6,060,711 width=43) (actual time=310,804.466..557,685.881 rows=4,344,197 loops=3)

  • Hash Cond: (lineitem.l_orderkey = orders.o_orderkey)
7. 76,970.316 409,125.474 ↑ 1.4 4,344,197 3 / 3

Hash Join (cost=1,992,499.84..11,262,543.51 rows=6,060,711 width=47) (actual time=59,079.243..409,125.474 rows=4,344,197 loops=3)

  • Hash Cond: ((lineitem.l_suppkey = partsupp.ps_suppkey) AND (lineitem.l_partkey = partsupp.ps_partkey))
8. 73,058.431 280,649.041 ↑ 1.4 4,344,197 3 / 3

Hash Join (cost=271,875.84..9,155,430.67 rows=6,060,711 width=45) (actual time=7,285.956..280,649.041 rows=4,344,197 loops=3)

  • Hash Cond: (lineitem.l_partkey = part.p_partkey)
9. 200,369.603 200,369.603 ↑ 1.2 80,004,097 3 / 3

Parallel Seq Scan on lineitem (cost=0.00..5,861,298.33 rows=100,001,633 width=41) (actual time=26.244..200,369.603 rows=80,004,097 loops=3)

10. 142.310 7,221.007 ↑ 1.1 434,469 3 / 3

Hash (cost=263,921.21..263,921.21 rows=484,850 width=4) (actual time=7,221.007..7,221.007 rows=434,469 loops=3)

  • Buckets: 131,072 Batches: 8 Memory Usage: 2,933kB
11. 7,078.697 7,078.697 ↑ 1.1 434,469 3 / 3

Seq Scan on part (cost=0.00..263,921.21 rows=484,850 width=4) (actual time=18.288..7,078.697 rows=434,469 loops=3)

  • Filter: ((p_name)::text ~~ _%orchid%_::text)
  • Rows Removed by Filter: 7,565,531
12. 11,115.743 51,506.117 ↑ 1.0 32,000,000 3 / 3

Hash (cost=1,053,030.40..1,053,030.40 rows=32,004,440 width=22) (actual time=51,506.117..51,506.117 rows=32,000,000 loops=3)

  • Buckets: 65,536 Batches: 512 Memory Usage: 3,941kB
13. 40,390.374 40,390.374 ↑ 1.0 32,000,000 3 / 3

Seq Scan on partsupp (cost=0.00..1,053,030.40 rows=32,004,440 width=22) (actual time=27.948..40,390.374 rows=32,000,000 loops=3)

14. 22,178.389 70,487.080 ↑ 1.0 60,000,000 3 / 3

Hash (cost=1,705,028.08..1,705,028.08 rows=60,007,308 width=8) (actual time=70,487.080..70,487.080 rows=60,000,000 loops=3)

  • Buckets: 131,072 Batches: 1,024 Memory Usage: 3,316kB
15. 48,308.691 48,308.691 ↑ 1.0 60,000,000 3 / 3

Seq Scan on orders (cost=0.00..1,705,028.08 rows=60,007,308 width=8) (actual time=0.049..48,308.691 rows=60,000,000 loops=3)

16. 70.081 339.486 ↑ 1.0 400,000 3 / 3

Hash (cost=18,239.94..18,239.94 rows=400,000 width=108) (actual time=339.486..339.486 rows=400,000 loops=3)

  • Buckets: 32,768 Batches: 16 Memory Usage: 1,788kB
17. 75.761 269.405 ↑ 1.0 400,000 3 / 3

Hash Join (cost=13.82..18,239.94 rows=400,000 width=108) (actual time=6.450..269.405 rows=400,000 loops=3)

  • Hash Cond: (supplier.s_nationkey = nation.n_nationkey)
18. 193.614 193.614 ↑ 1.0 400,000 3 / 3

Seq Scan on supplier (cost=0.00..13,197.00 rows=400,000 width=12) (actual time=6.404..193.614 rows=400,000 loops=3)

19. 0.010 0.030 ↑ 6.8 25 3 / 3

Hash (cost=11.70..11.70 rows=170 width=108) (actual time=0.030..0.030 rows=25 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
20. 0.020 0.020 ↑ 6.8 25 3 / 3

Seq Scan on nation (cost=0.00..11.70 rows=170 width=108) (actual time=0.015..0.020 rows=25 loops=3)

Planning time : 2.306 ms
Execution time : 574,637.533 ms