explain.depesz.com

PostgreSQL's explain analyze made readable

Result: A16I

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

GroupAggregate (cost=438,905.09..438,926.22 rows=153 width=66) (actual time=3,258.618..3,611.735 rows=175 loops=1)

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

Gather Merge (cost=438,905.09..438,922.27 rows=128 width=66) (actual time=3,256.494..3,688.968 rows=525 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
3. 207.260 3,532.478 ↓ 2.7 175 3 / 3

GroupAggregate (cost=437,905.07..437,907.47 rows=64 width=66) (actual time=3,249.935..3,532.478 rows=175 loops=3)

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

Sort (cost=437,905.07..437,905.23 rows=64 width=57) (actual time=3,248.963..3,325.218 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: 19,048kB
5. 84.131 2,883.588 ↓ 3,375.0 215,999 3 / 3

Hash Join (cost=368,528.18..437,903.15 rows=64 width=57) (actual time=2,478.807..2,883.588 rows=215,999 loops=3)

6. 659.555 2,799.436 ↓ 3,375.0 215,999 3 / 3

Hash Join (cost=368,526.62..437,900.39 rows=64 width=31) (actual time=2,478.752..2,799.436 rows=215,999 loops=3)

7. 129.975 129.975 ↑ 1.2 1,000,000 3 / 3

Seq Scan on orders orders (cost=0..64,686 rows=1,250,000 width=8) (actual time=0.008..129.975 rows=1,000,000 loops=3)

8. 124.402 2,009.906 ↓ 3,375.0 215,999 3 / 3

Hash (cost=368,525.82..368,525.82 rows=64 width=31) (actual time=2,009.906..2,009.906 rows=215,999 loops=3)

9. 122.121 1,885.504 ↓ 3,375.0 215,999 3 / 3

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

10. 1,198.698 1,755.026 ↓ 3,375.0 215,999 3 / 3

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

11. 359.019 359.019 ↑ 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.005..359.019 rows=3,999,332 loops=3)

12. 10.330 197.309 ↑ 1.4 28,864 3 / 3

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

13. 76.922 186.979 ↑ 1.4 28,864 3 / 3

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

14. 73.206 73.206 ↑ 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..73.206 rows=533,333 loops=3)

15. 1.762 36.851 ↑ 1.4 7,216 3 / 3

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

16. 35.089 35.089 ↑ 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.089 rows=7,216 loops=3)

  • Filter: ((p_name)::text ~~ '%royal%'::text)
17. 3.981 8.357 ↑ 1.0 20,000 3 / 3

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

18. 4.376 4.376 ↑ 1.0 20,000 3 / 3

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

19. 0.007 0.021 ↑ 1.0 25 3 / 3

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

20. 0.014 0.014 ↑ 1.0 25 3 / 3

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

Planning time : 0.989 ms
Execution time : 3,692.066 ms