explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Lb6t

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

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

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

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

  • Workers Planned: 2
  • Workers Launched: 2
3. 176.271 3,144.660 ↓ 2.7 175 3 / 3

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

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

Sort (cost=368,561.07..368,561.23 rows=64 width=57) (actual time=2,903.243..2,968.389 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: 20,416kB
5. 91.059 2,548.530 ↓ 3,375.0 215,999 3 / 3

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

6. 176.419 2,457.449 ↓ 3,375.0 215,999 3 / 3

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

7. 111.361 1,849.033 ↓ 3,375.0 215,999 3 / 3

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

8. 1,166.365 1,729.344 ↓ 3,375.0 215,999 3 / 3

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

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

10. 9.983 196.516 ↑ 1.4 28,864 3 / 3

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

11. 77.510 186.533 ↑ 1.4 28,864 3 / 3

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

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

13. 1.674 36.052 ↑ 1.4 7,216 3 / 3

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

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

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

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

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

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

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

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

Index Only Scan using orders_key_date 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)
  • Heap Fetches: 647,996
18. 0.008 0.022 ↑ 1.0 25 3 / 3

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

19. 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 : 1.058 ms
Execution time : 3,238.184 ms