explain.depesz.com

PostgreSQL's explain analyze made readable

Result: F4Vl

Settings
# exclusive inclusive rows x rows loops node
1. 0.173 194,776.790 ↓ 15.1 211 1

Sort (cost=14,212.82..14,212.86 rows=14 width=61) (actual time=194,776.774..194,776.790 rows=211 loops=1)

  • Sort Key: tmp.year, croptype.name_ru
  • Sort Method: quicksort Memory: 43kB
2. 0.057 194,776.617 ↓ 15.1 211 1

Hash Right Join (cost=14,209.77..14,212.55 rows=14 width=61) (actual time=194,776.564..194,776.617 rows=211 loops=1)

  • Hash Cond: (croptype.id = tmp.crop_type_id)
3. 0.008 0.008 ↓ 1.0 48 1

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

4. 0.039 194,776.552 ↓ 15.1 211 1

Hash (cost=14,209.59..14,209.59 rows=14 width=48) (actual time=194,776.552..194,776.552 rows=211 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
5. 0.019 194,776.513 ↓ 15.1 211 1

Subquery Scan on tmp (cost=14,209.28..14,209.59 rows=14 width=48) (actual time=194,776.422..194,776.513 rows=211 loops=1)

6. 108.569 194,776.494 ↓ 15.1 211 1

HashAggregate (cost=14,209.28..14,209.45 rows=14 width=15) (actual time=194,776.422..194,776.494 rows=211 loops=1)

  • Group Key: proagrica_aoiyeardata.year, proagrica_aoiyeardata.crop_type_id
7. 84.672 194,667.925 ↓ 4,955.4 69,376 1

Merge Join (cost=1,867.24..14,209.14 rows=14 width=15) (actual time=13.639..194,667.925 rows=69,376 loops=1)

  • Merge Cond: (aoi.user_id = proagrica_user.id)
8. 47.606 194,541.281 ↓ 78.5 69,376 1

Nested Loop Left Join (cost=0.86..773,301.77 rows=884 width=19) (actual time=0.086..194,541.281 rows=69,376 loops=1)

9. 193,930.566 194,263.881 ↓ 44.5 38,299 1

Nested Loop (cost=0.43..762,123.81 rows=860 width=11) (actual time=0.079..194,263.881 rows=38,299 loops=1)

  • Join Filter: ((region.geom ~ aoi.center) AND _st_contains(region.geom, aoi.center))
  • Rows Removed by Join Filter: 1938317
10. 186.899 186.899 ↑ 1.3 73,208 1

Index Scan using proagrica_areaofinterest_user_id_4fcfdaec on proagrica_areaofinterest aoi (cost=0.43..78,254.73 rows=95,579 width=43) (actual time=0.010..186.899 rows=73,208 loops=1)

  • Index Cond: (user_id IS NOT NULL)
  • Filter: (NOT is_removed)
  • Rows Removed by Filter: 21772
11. 146.398 146.416 ↑ 1.0 27 73,208

Materialize (cost=0.00..1.41 rows=27 width=83,641) (actual time=0.000..0.002 rows=27 loops=73,208)

12. 0.018 0.018 ↑ 1.0 27 1

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

13. 229.794 229.794 ↑ 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.005..0.006 rows=1 loops=38,299)

  • Index Cond: (aoi_id = aoi.id)
14. 37.916 41.972 ↓ 4.4 83,378 1

Sort (cost=1,866.36..1,913.81 rows=18,978 width=4) (actual time=13.548..41.972 rows=83,378 loops=1)

  • Sort Key: proagrica_user.id
  • Sort Method: quicksort Memory: 1660kB
15. 4.056 4.056 ↓ 1.0 19,014 1

Seq Scan on proagrica_user (cost=0.00..517.78 rows=18,978 width=4) (actual time=0.004..4.056 rows=19,014 loops=1)

Planning time : 0.521 ms
Execution time : 194,777.409 ms