explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MtQ3r

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

Hash Left Join (cost=30,883.82..63,696,771.34 rows=50,452 width=302) (actual time=954.504..3,964.484 rows=29,056 loops=1)

  • Hash Cond: ((lc.mid = lcu.midcontract) AND (c.mid = lcu.midcrop))
2.          

CTE square_by_crop

3. 16.378 944.142 ↑ 168.7 299 1

HashAggregate (cost=29,824.05..30,454.70 rows=50,452 width=40) (actual time=943.951..944.142 rows=299 loops=1)

  • Group Key: l_1.mid, c_1.mid
4. 12.385 927.764 ↑ 1.7 29,056 1

Hash Join (cost=17,585.15..29,319.53 rows=50,452 width=40) (actual time=635.824..927.764 rows=29,056 loops=1)

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

Hash Join (cost=17,506.97..28,547.65 rows=50,452 width=40) (actual time=634.233..913.816 rows=29,056 loops=1)

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

Hash Join (cost=17,397.09..27,644.71 rows=76,944 width=40) (actual time=632.105..888.066 rows=74,707 loops=1)

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

Index Only Scan using agricultureusing_curr_year_delete on agricultureusing au_1 (cost=0.42..8,658.16 rows=76,944 width=32) (actual time=0.012..107.696 rows=74,707 loops=1)

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

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

  • Buckets: 524288 Batches: 2 Memory Usage: 23293kB
9. 390.027 390.027 ↑ 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.022..390.027 rows=546,110 loops=1)

  • Index Cond: (is_deleted = false)
  • Filter: (NOT is_deleted)
  • Heap Fetches: 24
10. 0.885 2.097 ↑ 1.0 3,110 1

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

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

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

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

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

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

14. 10.916 1,345.603 ↑ 1.7 29,056 1

Hash Left Join (cost=428.39..30,577.53 rows=50,452 width=313) (actual time=10.168..1,345.603 rows=29,056 loops=1)

  • Hash Cond: ((au.myear = lc.myear) AND (l.mid = lc.midlayer))
15. 10.748 1,334.649 ↑ 1.7 29,056 1

Hash Left Join (cost=427.45..30,068.88 rows=50,452 width=301) (actual time=10.121..1,334.649 rows=29,056 loops=1)

  • Hash Cond: (f.mregion = r.mid)
16. 17.875 1,323.866 ↑ 1.7 29,056 1

Hash Join (cost=426.65..29,811.35 rows=50,452 width=270) (actual time=10.078..1,323.866 rows=29,056 loops=1)

  • Hash Cond: (au.mcrops = c.mid)
17. 29.977 1,300.559 ↑ 1.7 29,056 1

Hash Join (cost=235.49..28,926.48 rows=50,452 width=245) (actual time=4.618..1,300.559 rows=29,056 loops=1)

  • Hash Cond: (f.mlayer = l.mid)
18. 105.215 1,266.051 ↑ 1.0 74,707 1

Merge Join (cost=93.18..27,991.10 rows=76,944 width=76) (actual time=0.060..1,266.051 rows=74,707 loops=1)

  • Merge Cond: (f.mid = au.mfeature)
19. 1,031.701 1,031.701 ↑ 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.013..1,031.701 rows=546,110 loops=1)

  • Filter: (NOT is_deleted)
20. 129.135 129.135 ↑ 1.0 74,707 1

Index Only Scan using agricultureusing_curr_year_delete on agricultureusing au (cost=0.42..8,658.16 rows=76,944 width=36) (actual time=0.013..129.135 rows=74,707 loops=1)

  • Index Cond: ((myear = 2019) AND (is_deleted = false))
  • Filter: (NOT is_deleted)
  • Heap Fetches: 74707
21. 1.602 4.531 ↑ 1.0 3,110 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 395kB
22. 1.762 2.929 ↑ 1.0 3,110 1

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

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

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
25. 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.008..0.013 rows=22 loops=1)

26. 1.309 5.432 ↑ 1.0 3,221 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 265kB
27. 1.827 4.123 ↑ 1.0 3,221 1

Hash Join (cost=68.70..150.90 rows=3,221 width=41) (actual time=1.808..4.123 rows=3,221 loops=1)

  • Hash Cond: (c.mstyle = s.mid)
28. 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.018..0.535 rows=3,221 loops=1)

29. 0.886 1.761 ↑ 1.0 2,871 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 187kB
30. 0.875 0.875 ↑ 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.875 rows=2,871 loops=1)

31. 0.019 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
32. 0.016 0.016 ↑ 1.0 31 1

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

33. 0.018 0.038 ↓ 1.0 31 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
34. 0.020 0.020 ↓ 1.0 31 1

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

  • Filter: (myear = 2019)
  • Rows Removed by Filter: 1
35. 0.017 0.029 ↓ 1.1 28 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
36. 0.012 0.012 ↓ 1.1 28 1

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

37.          

SubPlan (for Hash Left Join)

38. 2,585.984 2,585.984 ↑ 1.0 1 29,056

CTE Scan on square_by_crop (cost=0.00..1,261.30 rows=1 width=8) (actual time=0.062..0.089 rows=1 loops=29,056)

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