explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 34rX

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

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

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

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

  • Workers Planned: 2
  • Workers Launched: 2
3. 205.885 3,420.342 ↓ 2.7 175 3 / 3

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

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

Sort (cost=437,905.07..437,905.23 rows=64 width=57) (actual time=3,138.05..3,214.457 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,264kB
5. 80.943 2,787.305 ↓ 3,375.0 215,999 3 / 3

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

6. 641.243 2,706.341 ↓ 3,375.0 215,999 3 / 3

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

7. 127.897 127.897 ↑ 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..127.897 rows=1,000,000 loops=3)

8. 119.451 1,937.201 ↓ 3,375.0 215,999 3 / 3

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

9. 118.661 1,817.750 ↓ 3,375.0 215,999 3 / 3

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

10. 1,138.259 1,690.982 ↓ 3,375.0 215,999 3 / 3

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

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

12. 10.041 194.368 ↑ 1.4 28,864 3 / 3

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

13. 76.479 184.327 ↑ 1.4 28,864 3 / 3

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

14. 71.271 71.271 ↑ 1.2 533,333 3 / 3

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

15. 1.665 36.577 ↑ 1.4 7,216 3 / 3

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

16. 34.912 34.912 ↑ 1.4 7,216 3 / 3

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

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

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

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

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

19. 0.008 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.013 0.013 ↑ 1.0 25 3 / 3

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

Planning time : 1.004 ms
Execution time : 3,525.694 ms