explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Dxsk

Settings
# exclusive inclusive rows x rows loops node
1. 0.191 3,157.810 ↓ 3.8 211 1

Sort (cost=44,318.22..44,318.36 rows=55 width=61) (actual time=3,157.794..3,157.810 rows=211 loops=1)

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

Hash Left Join (cost=44,314.72..44,316.63 rows=55 width=61) (actual time=3,157.512..3,157.619 rows=211 loops=1)

  • Hash Cond: (proagrica_aoiyeardata.crop_type_id = croptype.id)
3. 38.730 3,157.533 ↓ 3.8 211 1

HashAggregate (cost=44,311.66..44,312.35 rows=55 width=15) (actual time=3,157.477..3,157.533 rows=211 loops=1)

  • Group Key: proagrica_aoiyeardata.year, proagrica_aoiyeardata.crop_type_id
4. 12.086 3,118.803 ↓ 78.5 69,380 1

Nested Loop Left Join (cost=1,097.59..44,302.82 rows=884 width=15) (actual time=23.342..3,118.803 rows=69,380 loops=1)

5. 5.906 2,991.817 ↓ 44.5 38,300 1

Nested Loop (cost=1,097.16..33,127.09 rows=860 width=7) (actual time=23.330..2,991.817 rows=38,300 loops=1)

6. 0.035 0.035 ↑ 1.0 27 1

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

7. 2,645.325 2,985.876 ↓ 44.3 1,419 27

Bitmap Heap Scan on proagrica_areaofinterest aoi (cost=1,097.16..1,226.56 rows=32 width=43) (actual time=14.760..110.588 rows=1,419 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=25259
8. 10.071 340.551 ↓ 0.0 0 27

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

9. 48.249 48.249 ↓ 1.4 8,605 27

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

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

Bitmap Index Scan on proagrica_areaofinterest_user_id_4fcfdaec (cost=0.00..992.95 rows=95,962 width=0) (actual time=10.453..10.453 rows=94,997 loops=27)

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

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.003 rows=1 loops=38,300)

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

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

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

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

Planning time : 0.411 ms
Execution time : 3,158.302 ms