explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HMEl

Settings
# exclusive inclusive rows x rows loops node
1. 85.931 3,688.073 ↑ 1.7 29,056 1

HashAggregate (cost=62,405.44..63,622,613.87 rows=50,422 width=350) (actual time=2,168.424..3,688.073 rows=29,056 loops=1)

  • Group Key: f.mid, au.mid, l.mid, c.mid, cl.mid, r.mid, s.mid
2.          

CTE square_by_crop

3. 15.336 907.500 ↑ 168.6 299 1

HashAggregate (cost=29,817.16..30,447.43 rows=50,422 width=40) (actual time=907.286..907.500 rows=299 loops=1)

  • Group Key: l_1.mid, c_1.mid
4. 12.062 892.164 ↑ 1.7 29,056 1

Hash Join (cost=17,585.15..29,312.94 rows=50,422 width=40) (actual time=590.173..892.164 rows=29,056 loops=1)

  • Hash Cond: (au_1.mcrops = c_1.mid)
5. 23.022 878.396 ↑ 1.7 29,056 1

Hash Join (cost=17,506.97..28,541.46 rows=50,422 width=40) (actual time=588.424..878.396 rows=29,056 loops=1)

  • Hash Cond: (f_1.mlayer = l_1.mid)
6. 159.559 853.075 ↑ 1.0 74,707 1

Hash Join (cost=17,397.09..27,639.00 rows=76,897 width=40) (actual time=586.094..853.075 rows=74,707 loops=1)

  • Hash Cond: (au_1.mfeature = f_1.mid)
7. 110.426 110.426 ↑ 1.0 74,707 1

Index Only Scan using agricultureusing_curr_year_delete on agricultureusing au_1 (cost=0.42..8,653.10 rows=76,897 width=32) (actual time=0.015..110.426 rows=74,707 loops=1)

  • Index Cond: ((myear = 2019) AND (is_deleted = false))
  • Filter: (NOT is_deleted)
  • Heap Fetches: 74707
8. 220.572 583.090 ↑ 1.0 546,110 1

Hash (cost=10,143.29..10,143.29 rows=546,134 width=40) (actual time=583.090..583.090 rows=546,110 loops=1)

  • Buckets: 524288 Batches: 2 Memory Usage: 23293kB
9. 362.518 362.518 ↑ 1.0 546,110 1

Index Only Scan using ind_vsxfeature_complex on vsxfeature f_1 (cost=0.55..10,143.29 rows=546,134 width=40) (actual time=0.021..362.518 rows=546,110 loops=1)

  • Index Cond: (is_deleted = false)
  • Filter: (NOT is_deleted)
  • Heap Fetches: 0
10. 0.933 2.299 ↑ 1.0 3,110 1

Hash (cost=71.00..71.00 rows=3,110 width=16) (actual time=2.299..2.299 rows=3,110 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 178kB
11. 1.366 1.366 ↑ 1.0 3,110 1

Index Scan using ind_fk_vsxlayers_mowner on vsxlayers l_1 (cost=0.28..71.00 rows=3,110 width=16) (actual time=0.027..1.366 rows=3,110 loops=1)

  • Index Cond: (mowner = '79610a40-aeaa-4bff-a9d6-5444a5aa94c9'::uuid)
12. 0.926 1.706 ↑ 1.0 3,221 1

Hash (cost=37.91..37.91 rows=3,221 width=16) (actual time=1.706..1.706 rows=3,221 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 183kB
13. 0.780 0.780 ↑ 1.0 3,221 1

Seq Scan on crops c_1 (cost=0.00..37.91 rows=3,221 width=16) (actual time=0.021..0.780 rows=3,221 loops=1)

14. 8.452 1,219.550 ↑ 1.7 29,056 1

Hash Left Join (cost=429.11..30,949.56 rows=50,422 width=337) (actual time=10.395..1,219.550 rows=29,056 loops=1)

  • Hash Cond: ((lc.mid = lcu.midcontract) AND (c.mid = lcu.midcrop))
15. 9.057 1,211.071 ↑ 1.7 29,056 1

Hash Left Join (cost=428.39..30,570.54 rows=50,422 width=345) (actual time=10.357..1,211.071 rows=29,056 loops=1)

  • Hash Cond: ((au.myear = lc.myear) AND (l.mid = lc.midlayer))
16. 8.864 1,201.979 ↑ 1.7 29,056 1

Hash Left Join (cost=427.45..30,062.20 rows=50,422 width=333) (actual time=10.312..1,201.979 rows=29,056 loops=1)

  • Hash Cond: (f.mregion = r.mid)
17. 14.665 1,193.080 ↑ 1.7 29,056 1

Hash Join (cost=426.65..29,804.82 rows=50,422 width=302) (actual time=10.265..1,193.080 rows=29,056 loops=1)

  • Hash Cond: (au.mcrops = c.mid)
18. 24.156 1,172.756 ↑ 1.7 29,056 1

Hash Join (cost=235.49..28,920.35 rows=50,422 width=261) (actual time=4.579..1,172.756 rows=29,056 loops=1)

  • Hash Cond: (f.mlayer = l.mid)
19. 85.099 1,144.109 ↑ 1.0 74,707 1

Merge Join (cost=93.18..27,985.45 rows=76,897 width=92) (actual time=0.059..1,144.109 rows=74,707 loops=1)

  • Merge Cond: (f.mid = au.mfeature)
20. 924.607 924.607 ↑ 1.0 546,110 1

Index Scan using vsxfeature_pkey on vsxfeature f (cost=0.42..17,098.13 rows=546,134 width=56) (actual time=0.014..924.607 rows=546,110 loops=1)

  • Filter: (NOT is_deleted)
21. 134.403 134.403 ↑ 1.0 74,707 1

Index Scan using agricultureusing_curr_year_delete on agricultureusing au (cost=0.42..8,653.10 rows=76,897 width=52) (actual time=0.013..134.403 rows=74,707 loops=1)

  • Index Cond: ((myear = 2019) AND (is_deleted = false))
  • Filter: (NOT is_deleted)
22. 1.608 4.491 ↑ 1.0 3,110 1

Hash (cost=103.44..103.44 rows=3,110 width=185) (actual time=4.491..4.491 rows=3,110 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 395kB
23. 1.744 2.883 ↑ 1.0 3,110 1

Hash Left Join (cost=0.88..103.44 rows=3,110 width=185) (actual time=0.054..2.883 rows=3,110 loops=1)

  • Hash Cond: (l.mcluster = cl.mid)
24. 1.114 1.114 ↑ 1.0 3,110 1

Index Scan using ind_fk_vsxlayers_mowner on vsxlayers l (cost=0.28..71.00 rows=3,110 width=165) (actual time=0.019..1.114 rows=3,110 loops=1)

  • Index Cond: (mowner = '79610a40-aeaa-4bff-a9d6-5444a5aa94c9'::uuid)
25. 0.012 0.025 ↑ 1.0 22 1

Hash (cost=0.32..0.32 rows=22 width=36) (actual time=0.025..0.025 rows=22 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
26. 0.013 0.013 ↑ 1.0 22 1

Seq Scan on clusters cl (cost=0.00..0.32 rows=22 width=36) (actual time=0.009..0.013 rows=22 loops=1)

27. 1.486 5.659 ↑ 1.0 3,221 1

Hash (cost=150.90..150.90 rows=3,221 width=57) (actual time=5.659..5.659 rows=3,221 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 315kB
28. 1.875 4.173 ↑ 1.0 3,221 1

Hash Join (cost=68.70..150.90 rows=3,221 width=57) (actual time=1.810..4.173 rows=3,221 loops=1)

  • Hash Cond: (c.mstyle = s.mid)
29. 0.535 0.535 ↑ 1.0 3,221 1

Seq Scan on crops c (cost=0.00..37.91 rows=3,221 width=50) (actual time=0.019..0.535 rows=3,221 loops=1)

30. 0.883 1.763 ↑ 1.0 2,871 1

Hash (cost=32.81..32.81 rows=2,871 width=23) (actual time=1.763..1.763 rows=2,871 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 187kB
31. 0.880 0.880 ↑ 1.0 2,871 1

Seq Scan on vsxstyles s (cost=0.00..32.81 rows=2,871 width=23) (actual time=0.009..0.880 rows=2,871 loops=1)

32. 0.017 0.035 ↑ 1.0 31 1

Hash (cost=0.41..0.41 rows=31 width=35) (actual time=0.035..0.035 rows=31 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
33. 0.018 0.018 ↑ 1.0 31 1

Seq Scan on regions r (cost=0.00..0.41 rows=31 width=35) (actual time=0.011..0.018 rows=31 loops=1)

34. 0.017 0.035 ↑ 1.0 30 1

Hash (cost=0.49..0.49 rows=30 width=36) (actual time=0.035..0.035 rows=30 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
35. 0.018 0.018 ↑ 1.0 30 1

Seq Scan on leasecontracts lc (cost=0.00..0.49 rows=30 width=36) (actual time=0.008..0.018 rows=30 loops=1)

  • Filter: (myear = 2019)
  • Rows Removed by Filter: 1
36. 0.016 0.027 ↑ 1.0 25 1

Hash (cost=0.35..0.35 rows=25 width=40) (actual time=0.027..0.027 rows=25 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
37. 0.011 0.011 ↑ 1.0 25 1

Seq Scan on leasecontractunits lcu (cost=0.00..0.35 rows=25 width=40) (actual time=0.006..0.011 rows=25 loops=1)

38.          

SubPlan (for HashAggregate)

39. 2,382.592 2,382.592 ↑ 1.0 1 29,056

CTE Scan on square_by_crop (cost=0.00..1,260.55 rows=1 width=8) (actual time=0.058..0.082 rows=1 loops=29,056)

  • Filter: ((layer = l.mid) AND (c.mid = crop))
  • Rows Removed by Filter: 298
Planning time : 6.613 ms
Execution time : 3,700.917 ms