explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HOb6

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

HashAggregate (cost=51,863.10..63,612,071.53 rows=50,422 width=350) (actual time=1,671.920..3,203.542 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. 8.526 691.317 ↑ 168.6 299 1

HashAggregate (cost=24,581.14..25,211.41 rows=50,422 width=40) (actual time=691.181..691.317 rows=299 loops=1)

  • Group Key: l_1.mid, c_1.mid
4. 6.590 682.791 ↑ 1.7 29,056 1

Hash Join (cost=17,602.31..24,076.92 rows=50,422 width=40) (actual time=526.050..682.791 rows=29,056 loops=1)

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

Hash Join (cost=17,506.25..23,287.56 rows=50,422 width=40) (actual time=522.445..672.642 rows=29,056 loops=1)

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

Hash Join (cost=17,396.38..22,385.09 rows=76,897 width=40) (actual time=519.834..659.053 rows=74,707 loops=1)

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

Index Scan using agricultureusing_curr_year_delete on agricultureusing au_1 (cost=0.42..3,399.91 rows=76,897 width=32) (actual time=0.027..40.467 rows=74,707 loops=1)

  • Index Cond: ((myear = 2019) AND (is_deleted = false))
  • Filter: (NOT is_deleted)
8. 195.703 517.329 ↑ 1.0 546,110 1

Hash (cost=10,142.88..10,142.88 rows=546,110 width=40) (actual time=517.329..517.329 rows=546,110 loops=1)

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

Index Only Scan using ind_vsxfeature_complex on vsxfeature f_1 (cost=0.55..10,142.88 rows=546,110 width=40) (actual time=0.023..321.626 rows=546,110 loops=1)

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

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

  • Buckets: 4096 Batches: 1 Memory Usage: 178kB
11. 1.354 1.354 ↑ 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.023..1.354 rows=3,110 loops=1)

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

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

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

Index Only Scan using crops_pkey on crops c_1 (cost=0.28..55.80 rows=3,221 width=16) (actual time=0.106..2.589 rows=3,221 loops=1)

  • Heap Fetches: 3221
14. 8.650 942.315 ↑ 1.7 29,056 1

Hash Left Join (cost=17,874.71..25,643.25 rows=50,422 width=337) (actual time=646.535..942.315 rows=29,056 loops=1)

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

Hash Left Join (cost=17,873.52..25,263.77 rows=50,422 width=345) (actual time=646.489..933.632 rows=29,056 loops=1)

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

Hash Left Join (cost=17,872.11..24,754.95 rows=50,422 width=333) (actual time=646.383..924.050 rows=29,056 loops=1)

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

Hash Join (cost=17,870.82..24,497.12 rows=50,422 width=302) (actual time=646.329..915.829 rows=29,056 loops=1)

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

Hash Join (cost=17,645.84..23,578.84 rows=50,422 width=261) (actual time=636.353..891.234 rows=29,056 loops=1)

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

Hash Join (cost=17,503.08..22,643.49 rows=76,897 width=92) (actual time=630.427..863.533 rows=74,707 loops=1)

  • Hash Cond: (au.mfeature = f.mid)
20. 57.038 57.038 ↑ 1.0 74,707 1

Index Scan using agricultureusing_curr_year_delete on agricultureusing au (cost=0.42..3,399.91 rows=76,897 width=52) (actual time=0.032..57.038 rows=74,707 loops=1)

  • Index Cond: ((myear = 2019) AND (is_deleted = false))
  • Filter: (NOT is_deleted)
21. 252.355 626.893 ↑ 1.0 546,110 1

Hash (cost=10,142.88..10,142.88 rows=546,110 width=56) (actual time=626.893..626.893 rows=546,110 loops=1)

  • Buckets: 524288 Batches: 2 Memory Usage: 26989kB
22. 374.538 374.538 ↑ 1.0 546,110 1

Index Only Scan using ind_vsxfeature_complex on vsxfeature f (cost=0.55..10,142.88 rows=546,110 width=56) (actual time=0.118..374.538 rows=546,110 loops=1)

  • Index Cond: (is_deleted = false)
  • Filter: (NOT is_deleted)
  • Heap Fetches: 0
23. 1.617 5.495 ↑ 1.0 3,110 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 395kB
24. 2.286 3.878 ↑ 1.0 3,110 1

Hash Left Join (cost=1.33..103.89 rows=3,110 width=185) (actual time=0.056..3.878 rows=3,110 loops=1)

  • Hash Cond: (l.mcluster = cl.mid)
25. 1.564 1.564 ↑ 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.021..1.564 rows=3,110 loops=1)

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

Hash (cost=0.77..0.77 rows=22 width=36) (actual time=0.028..0.028 rows=22 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
27. 0.015 0.015 ↑ 1.0 22 1

Index Scan using clusters_pkey on clusters cl (cost=0.14..0.77 rows=22 width=36) (actual time=0.005..0.015 rows=22 loops=1)

28. 1.476 9.948 ↑ 1.0 3,221 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 315kB
29. 2.107 8.472 ↑ 1.0 3,221 1

Hash Join (cost=84.91..184.72 rows=3,221 width=57) (actual time=3.826..8.472 rows=3,221 loops=1)

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

Index Scan using crops_pkey on crops c (cost=0.28..55.80 rows=3,221 width=50) (actual time=0.011..2.565 rows=3,221 loops=1)

31. 0.937 3.800 ↑ 1.0 2,871 1

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

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

Index Scan using vsxstyle_pkey on vsxstyles s (cost=0.28..48.75 rows=2,871 width=23) (actual time=0.009..2.863 rows=2,871 loops=1)

33. 0.016 0.035 ↑ 1.0 31 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
34. 0.019 0.019 ↑ 1.0 31 1

Index Scan using regions_pkey on regions r (cost=0.14..0.90 rows=31 width=35) (actual time=0.007..0.019 rows=31 loops=1)

35. 0.024 0.097 ↑ 1.0 30 1

Hash (cost=0.96..0.96 rows=30 width=36) (actual time=0.097..0.097 rows=30 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
36. 0.073 0.073 ↑ 1.0 30 1

Index Scan using leasecontracts_idx_myear on leasecontracts lc (cost=0.14..0.96 rows=30 width=36) (actual time=0.061..0.073 rows=30 loops=1)

  • Index Cond: (myear = 2019)
37. 0.016 0.033 ↑ 1.0 25 1

Hash (cost=0.81..0.81 rows=25 width=40) (actual time=0.033..0.033 rows=25 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
38. 0.017 0.017 ↑ 1.0 25 1

Index Scan using leasecontractunits_idx on leasecontractunits lcu (cost=0.14..0.81 rows=25 width=40) (actual time=0.007..0.017 rows=25 loops=1)

39.          

SubPlan (for HashAggregate)

40. 2,179.200 2,179.200 ↑ 1.0 1 29,056

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

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