explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2doR : 9

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

HashAggregate (cost=59,454.58..63,619,663.01 rows=50,422 width=350) (actual time=2,113.074..3,965.698 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. 14.323 965.483 ↑ 168.6 299 1

HashAggregate (cost=28,384.33..29,014.61 rows=50,422 width=40) (actual time=965.251..965.483 rows=299 loops=1)

  • Group Key: l_1.mid, c_1.mid
4. 11.403 951.160 ↑ 1.7 29,056 1

Hash Join (cost=21,597.75..27,880.11 rows=50,422 width=40) (actual time=725.767..951.160 rows=29,056 loops=1)

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

Hash Join (cost=21,519.58..27,108.64 rows=50,422 width=40) (actual time=724.013..938.054 rows=29,056 loops=1)

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

Hash Join (cost=21,409.70..26,206.18 rows=76,897 width=40) (actual time=721.289..916.806 rows=74,707 loops=1)

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

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

  • Index Cond: (myear = 2019)
8. 237.617 718.810 ↑ 1.0 546,110 1

Hash (cost=14,156.20..14,156.20 rows=546,110 width=40) (actual time=718.810..718.810 rows=546,110 loops=1)

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

Seq Scan on vsxfeature f_1 (cost=0.00..14,156.20 rows=546,110 width=40) (actual time=0.016..481.193 rows=546,110 loops=1)

10. 0.915 2.260 ↑ 1.0 3,110 1

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

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

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

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

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

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

14. 9.230 1,106.603 ↑ 1.7 29,056 1

Hash Left Join (cost=21,852.34..29,431.53 rows=50,422 width=337) (actual time=820.376..1,106.603 rows=29,056 loops=1)

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

Hash Left Join (cost=21,851.61..29,052.51 rows=50,422 width=345) (actual time=820.322..1,097.337 rows=29,056 loops=1)

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

Hash Left Join (cost=21,850.67..28,544.16 rows=50,422 width=333) (actual time=820.268..1,086.952 rows=29,056 loops=1)

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

Hash Join (cost=21,849.88..28,283.94 rows=50,422 width=302) (actual time=820.170..1,077.805 rows=29,056 loops=1)

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

Hash Join (cost=21,658.72..27,399.48 rows=50,422 width=261) (actual time=813.932..1,055.723 rows=29,056 loops=1)

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

Hash Join (cost=21,516.40..26,464.58 rows=76,897 width=92) (actual time=808.763..1,028.287 rows=74,707 loops=1)

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

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

  • Index Cond: (myear = 2019)
21. 293.820 806.189 ↑ 1.0 546,110 1

Hash (cost=14,156.20..14,156.20 rows=546,110 width=56) (actual time=806.189..806.189 rows=546,110 loops=1)

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

Seq Scan on vsxfeature f (cost=0.00..14,156.20 rows=546,110 width=56) (actual time=0.017..512.369 rows=546,110 loops=1)

23. 1.709 4.645 ↑ 1.0 3,110 1

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

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

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

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

  • Index Cond: (mowner = '79610a40-aeaa-4bff-a9d6-5444a5aa94c9'::uuid)
26. 0.013 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
27. 0.014 0.014 ↑ 1.0 22 1

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

28. 1.482 6.211 ↑ 1.0 3,221 1

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

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

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

  • Hash Cond: (c.mstyle = s.mid)
30. 0.885 0.885 ↑ 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.885 rows=3,221 loops=1)

31. 0.897 1.910 ↑ 1.0 2,871 1

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

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

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

33. 0.027 0.081 ↑ 1.0 31 1

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

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

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

35. 0.020 0.042 ↑ 1.0 30 1

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

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

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

  • Filter: (myear = 2019)
  • Rows Removed by Filter: 1
37. 0.023 0.036 ↑ 1.0 25 1

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

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

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

39.          

SubPlan (for HashAggregate)

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

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

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