explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WdZ1

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 3,250.788 ↓ 1.1 175 1

GroupAggregate (cost=369,561.09..369,582.22 rows=153 width=66) (actual time=2,980.431..3,250.788 rows=175 loops=1)

  • Group Key: nation.n_name, (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone))
2. 56.300 3,254.153 ↓ 4.1 525 1

Gather Merge (cost=369,561.09..369,578.27 rows=128 width=66) (actual time=2,978.508..3,254.153 rows=525 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
3. 169.324 3,197.853 ↓ 2.7 175 3 / 3

GroupAggregate (cost=368,561.07..368,563.47 rows=64 width=66) (actual time=2,966.608..3,197.853 rows=175 loops=3)

  • Group Key: nation.n_name, (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone))
4. 427.094 3,028.529 ↓ 3,375.0 215,999 3 / 3

Sort (cost=368,561.07..368,561.23 rows=64 width=57) (actual time=2,965.618..3,028.529 rows=215,999 loops=3)

  • Sort Key: nation.n_name, (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone)) DESC
  • Sort Method: external merge Disk: 13,912kB
5. 91.775 2,601.435 ↓ 3,375.0 215,999 3 / 3

Hash Join (cost=56,121.27..368,559.15 rows=64 width=57) (actual time=200.94..2,601.435 rows=215,999 loops=3)

6. 178.853 2,509.642 ↓ 3,375.0 215,999 3 / 3

Nested Loop (cost=56,119.71..368,556.38 rows=64 width=31) (actual time=200.893..2,509.642 rows=215,999 loops=3)

7. 113.313 1,898.792 ↓ 3,375.0 215,999 3 / 3

Hash Join (cost=56,119.28..368,525.82 rows=64 width=31) (actual time=200.87..1,898.792 rows=215,999 loops=3)

8. 1,212.635 1,777.579 ↓ 3,375.0 215,999 3 / 3

Hash Join (cost=55,226.28..367,631.94 rows=64 width=35) (actual time=192.871..1,777.579 rows=215,999 loops=3)

9. 372.359 372.359 ↑ 1.2 3,999,332 3 / 3

Seq Scan on lineitem lineitem (cost=0..274,911.65 rows=4,999,165 width=29) (actual time=0.004..372.359 rows=3,999,332 loops=3)

10. 9.685 192.585 ↑ 1.4 28,864 3 / 3

Hash (cost=54,620.31..54,620.31 rows=40,398 width=18) (actual time=192.585..192.585 rows=28,864 loops=3)

11. 75.045 182.900 ↑ 1.4 28,864 3 / 3

Hash Join (cost=10,402.6..54,620.31 rows=40,398 width=18) (actual time=37.014..182.9 rows=28,864 loops=3)

12. 70.952 70.952 ↑ 1.2 533,333 3 / 3

Seq Scan on partsupp partsupp (cost=0..41,549.73 rows=666,573 width=14) (actual time=0.006..70.952 rows=533,333 loops=3)

13. 1.704 36.903 ↑ 1.4 7,216 3 / 3

Hash (cost=10,276.33..10,276.33 rows=10,101 width=4) (actual time=36.902..36.903 rows=7,216 loops=3)

14. 35.199 35.199 ↑ 1.4 7,216 3 / 3

Seq Scan on part part (cost=0..10,276.33 rows=10,101 width=4) (actual time=0.012..35.199 rows=7,216 loops=3)

  • Filter: ((p_name)::text ~~ '%royal%'::text)
15. 3.774 7.900 ↑ 1.0 20,000 3 / 3

Hash (cost=643..643 rows=20,000 width=8) (actual time=7.9..7.9 rows=20,000 loops=3)

16. 4.126 4.126 ↑ 1.0 20,000 3 / 3

Seq Scan on supplier supplier (cost=0..643 rows=20,000 width=8) (actual time=0.006..4.126 rows=20,000 loops=3)

17. 431.997 431.997 ↑ 1.0 1 647,996 / 3

Index Scan using orders_key on orders orders (cost=0.43..0.47 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=647,996)

  • Index Cond: (o_orderkey = lineitem.l_orderkey)
18. 0.006 0.018 ↑ 1.0 25 3 / 3

Hash (cost=1.25..1.25 rows=25 width=30) (actual time=0.018..0.018 rows=25 loops=3)

19. 0.012 0.012 ↑ 1.0 25 3 / 3

Seq Scan on nation nation (cost=0..1.25 rows=25 width=30) (actual time=0.008..0.012 rows=25 loops=3)

Planning time : 0.98 ms
Execution time : 3,257.131 ms