explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xf9G

Settings
# exclusive inclusive rows x rows loops node
1. 0.192 2,989.306 ↓ 3.8 211 1

Sort (cost=44,317.59..44,317.72 rows=55 width=61) (actual time=2,989.290..2,989.306 rows=211 loops=1)

  • Sort Key: proagrica_aoiyeardata.year, croptype.name_ru
  • Sort Method: quicksort Memory: 43kB
2. 0.052 2,989.114 ↓ 3.8 211 1

Hash Left Join (cost=44,314.08..44,316.00 rows=55 width=61) (actual time=2,989.007..2,989.114 rows=211 loops=1)

  • Hash Cond: (proagrica_aoiyeardata.crop_type_id = croptype.id)
3. 32.656 2,989.041 ↓ 3.8 211 1

HashAggregate (cost=44,311.02..44,311.71 rows=55 width=15) (actual time=2,988.975..2,989.041 rows=211 loops=1)

  • Group Key: proagrica_aoiyeardata.year, proagrica_aoiyeardata.crop_type_id
4. 37.715 2,956.385 ↓ 78.5 69,376 1

Nested Loop Left Join (cost=1,097.56..44,302.18 rows=884 width=15) (actual time=25.404..2,956.385 rows=69,376 loops=1)

5. 12.142 2,842.072 ↓ 44.5 38,299 1

Nested Loop (cost=1,097.13..33,126.46 rows=860 width=7) (actual time=25.392..2,842.072 rows=38,299 loops=1)

6. 0.033 0.033 ↑ 1.0 27 1

Seq Scan on region (cost=0.00..1.27 rows=27 width=83,641) (actual time=0.003..0.033 rows=27 loops=1)

7. 2,540.646 2,829.897 ↓ 44.3 1,418 27

Bitmap Heap Scan on proagrica_areaofinterest aoi (cost=1,097.13..1,226.54 rows=32 width=39) (actual time=12.916..104.811 rows=1,418 loops=27)

  • Recheck Cond: ((region.geom ~ center) AND (user_id IS NOT NULL))
  • Filter: ((NOT is_removed) AND _st_contains(region.geom, center))
  • Rows Removed by Filter: 2230
  • Heap Blocks: exact=25255
8. 10.827 289.251 ↓ 0.0 0 27

BitmapAnd (cost=1,097.13..1,097.13 rows=96 width=0) (actual time=10.713..10.713 rows=0 loops=27)

9. 48.870 48.870 ↓ 1.4 8,604 27

Bitmap Index Scan on proagrica_areaofinterest_center_id (cost=0.00..80.05 rows=5,985 width=0) (actual time=1.810..1.810 rows=8,604 loops=27)

  • Index Cond: (region.geom ~ center)
10. 229.554 229.554 ↑ 1.0 94,980 27

Bitmap Index Scan on proagrica_areaofinterest_user_id_4fcfdaec (cost=0.00..992.93 rows=95,959 width=0) (actual time=8.502..8.502 rows=94,980 loops=27)

  • Index Cond: (user_id IS NOT NULL)
11. 76.598 76.598 ↑ 17.0 1 38,299

Index Scan using proagrica_aoiyeardata_aoi_id_aa9a0631 on proagrica_aoiyeardata (cost=0.43..12.83 rows=17 width=12) (actual time=0.002..0.002 rows=1 loops=38,299)

  • Index Cond: (aoi_id = aoi.id)
12. 0.011 0.021 ↓ 1.0 48 1

Hash (cost=2.47..2.47 rows=47 width=21) (actual time=0.021..0.021 rows=48 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
13. 0.010 0.010 ↓ 1.0 48 1

Seq Scan on proagrica_croptype croptype (cost=0.00..2.47 rows=47 width=21) (actual time=0.004..0.010 rows=48 loops=1)

Planning time : 0.374 ms
Execution time : 2,989.906 ms