explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ko9j : q21-p2y

Settings
# exclusive inclusive rows x rows loops node
1. 90.525 744.176 ↑ 1.0 411 1

Nested Loop (cost=63,213.31..63,750.69 rows=411 width=100) (actual time=528.091..744.176 rows=411 loops=1)

2. 106.013 624.059 ↑ 1.0 411 1

HashAggregate (cost=63,213.05..63,530.55 rows=411 width=93) (actual time=527.709..624.059 rows=411 loops=1)

  • Group Key: l.tuidout
3. 4.301 518.046 ↑ 1.0 4,141 1

Nested Loop (cost=169.10..63,161.29 rows=4,141 width=93) (actual time=2.459..518.046 rows=4,141 loops=1)

4. 4.118 464.053 ↑ 1.0 4,141 1

Hash Join (cost=168.85..63,078.22 rows=4,141 width=61) (actual time=2.350..464.053 rows=4,141 loops=1)

  • Hash Cond: (j.tuidout = l.tuidin)
5. 5.128 458.421 ↑ 1.0 4,141 1

Nested Loop (cost=42.32..62,894.76 rows=4,141 width=61) (actual time=0.805..458.421 rows=4,141 loops=1)

6. 3.366 109.590 ↑ 1.0 4,141 1

Hash Join (cost=7.15..62,776.76 rows=4,141 width=283) (actual time=0.178..109.590 rows=4,141 loops=1)

  • Hash Cond: (j.tuid4 = n.tuid)
7. 2.218 106.202 ↑ 1.0 4,141 1

Nested Loop (cost=5.59..62,718.26 rows=4,141 width=237) (actual time=0.144..106.202 rows=4,141 loops=1)

8. 4.870 66.715 ↑ 1.0 4,141 1

Merge Join (cost=5.16..33,922.11 rows=4,141 width=191) (actual time=0.131..66.715 rows=4,141 loops=1)

  • Merge Cond: (j.tuid1 = s.tuid)
9. 5.092 56.603 ↑ 1.0 4,141 1

Nested Loop (cost=0.71..33,313.74 rows=4,141 width=120) (actual time=0.095..56.603 rows=4,141 loops=1)

10. 1.819 1.819 ↑ 1.0 4,141 1

Index Scan using join4_pkey on join4 j (cost=0.28..228.88 rows=4,141 width=20) (actual time=0.040..1.819 rows=4,141 loops=1)

  • Index Cond: ((location)::integer = (3)::integer)
11. 49.692 49.692 ↑ 1.0 1 4,141

Index Scan using lineitem_2_pkey on lineitem_2 l1 (cost=0.43..7.98 rows=1 width=104) (actual time=0.012..0.012 rows=1 loops=4,141)

  • Index Cond: (tuid = j.tuid2)
12. 5.242 5.242 ↓ 1.4 13,630 1

Index Scan using supplier_2_pkey on supplier_2 s (cost=0.29..537.28 rows=10,000 width=79) (actual time=0.005..5.242 rows=13,630 loops=1)

13. 37.269 37.269 ↑ 1.0 1 4,141

Index Scan using orders_2_pkey on orders_2 o (cost=0.43..6.94 rows=1 width=54) (actual time=0.008..0.009 rows=1 loops=4,141)

  • Index Cond: (tuid = j.tuid3)
14. 0.010 0.022 ↑ 1.0 25 1

Hash (cost=1.25..1.25 rows=25 width=54) (actual time=0.022..0.022 rows=25 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
15. 0.012 0.012 ↑ 1.0 25 1

Seq Scan on nation_2 n (cost=0.00..1.25 rows=25 width=54) (actual time=0.008..0.012 rows=25 loops=1)

16. 74.538 343.703 ↑ 1.0 1 4,141

Function Scan on toy _y_2 (cost=35.17..35.18 rows=1 width=32) (actual time=0.082..0.083 rows=1 loops=4,141)

17.          

SubPlan (for Function Scan)

18. 78.679 186.345 ↑ 1.0 1 4,141

Aggregate (cost=17.19..17.45 rows=1 width=32) (actual time=0.045..0.045 rows=1 loops=4,141)

19. 4.141 107.666 ↑ 1.0 1 4,141

Nested Loop (cost=1.11..17.18 rows=1 width=32) (actual time=0.024..0.026 rows=1 loops=4,141)

20. 8.282 45.551 ↑ 1.0 1 4,141

Nested Loop (cost=0.85..16.90 rows=1 width=50) (actual time=0.010..0.011 rows=1 loops=4,141)

21. 24.846 24.846 ↑ 1.0 1 4,141

Index Only Scan using join2_pkey on join2 j_1 (cost=0.42..8.44 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=4,141)

  • Index Cond: ((location = (4)::integer) AND (tuid1 = l1.tuid))
  • Heap Fetches: 4141
22. 12.423 12.423 ↑ 1.0 1 4,141

Index Scan using lineitem_2_pkey on lineitem_2 l2 (cost=0.43..8.45 rows=1 width=54) (actual time=0.003..0.003 rows=1 loops=4,141)

  • Index Cond: (tuid = j_1.tuid2)
23. 57.974 57.974 ↑ 1.0 1 4,141

Function Scan on toy _y_3 (cost=0.26..0.27 rows=1 width=32) (actual time=0.014..0.014 rows=1 loops=4,141)

24. 53.833 82.820 ↑ 1.0 1 4,141

Aggregate (cost=17.19..17.45 rows=1 width=32) (actual time=0.020..0.020 rows=1 loops=4,141)

25. 4.141 28.987 ↓ 0.0 0 4,141

Nested Loop (cost=1.12..17.19 rows=1 width=32) (actual time=0.007..0.007 rows=0 loops=4,141)

26. 0.000 24.846 ↓ 0.0 0 4,141

Nested Loop (cost=0.85..16.90 rows=1 width=100) (actual time=0.006..0.006 rows=0 loops=4,141)

27. 24.846 24.846 ↓ 0.0 0 4,141

Index Only Scan using join2_pkey on join2 j_2 (cost=0.42..8.44 rows=1 width=4) (actual time=0.006..0.006 rows=0 loops=4,141)

  • Index Cond: ((location = (5)::integer) AND (tuid1 = l1.tuid))
  • Heap Fetches: 0
28. 0.000 0.000 ↓ 0.0 0

Index Scan using lineitem_2_pkey on lineitem_2 l3 (cost=0.43..8.45 rows=1 width=104) (never executed)

  • Index Cond: (tuid = j_2.tuid2)
29. 0.000 0.000 ↓ 0.0 0

Function Scan on toy _y_4 (cost=0.26..0.27 rows=1 width=32) (never executed)

30. 0.609 1.514 ↑ 1.0 4,141 1

Hash (cost=74.76..74.76 rows=4,141 width=8) (actual time=1.514..1.514 rows=4,141 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 226kB
31. 0.905 0.905 ↑ 1.0 4,141 1

Seq Scan on agg l (cost=0.00..74.76 rows=4,141 width=8) (actual time=0.015..0.905 rows=4,141 loops=1)

  • Filter: ((location)::integer = 2)
32. 49.692 49.692 ↑ 1.0 1 4,141

Function Scan on toy _y_1 (cost=0.25..0.26 rows=1 width=32) (actual time=0.012..0.012 rows=1 loops=4,141)

33. 29.592 29.592 ↑ 1.0 1 411

Function Scan on toy _y (cost=0.25..0.26 rows=1 width=32) (actual time=0.072..0.072 rows=1 loops=411)

Planning time : 11.920 ms
Execution time : 744.729 ms