explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CQ1T

Settings
# exclusive inclusive rows x rows loops node
1. 0.141 119,582.006 ↓ 25.3 177 1

Sort (cost=7,928.84..7,928.86 rows=7 width=61) (actual time=119,581.994..119,582.006 rows=177 loops=1)

  • Sort Key: tmp.year, croptype.name_ru
  • Sort Method: quicksort Memory: 40kB
2. 0.046 119,581.865 ↓ 25.3 177 1

Hash Right Join (cost=7,926.02..7,928.74 rows=7 width=61) (actual time=119,581.820..119,581.865 rows=177 loops=1)

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

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

4. 0.028 119,581.808 ↓ 25.3 177 1

Hash (cost=7,925.94..7,925.94 rows=7 width=48) (actual time=119,581.808..119,581.808 rows=177 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
5. 0.030 119,581.780 ↓ 25.3 177 1

Subquery Scan on tmp (cost=7,925.78..7,925.94 rows=7 width=48) (actual time=119,581.697..119,581.780 rows=177 loops=1)

6. 48.860 119,581.750 ↓ 25.3 177 1

HashAggregate (cost=7,925.78..7,925.87 rows=7 width=15) (actual time=119,581.695..119,581.750 rows=177 loops=1)

  • Group Key: proagrica_aoiyeardata.year, proagrica_aoiyeardata.crop_type_id
7. 32.015 119,532.890 ↓ 5,327.7 37,294 1

Nested Loop Left Join (cost=1,263.89..7,925.71 rows=7 width=15) (actual time=26.978..119,532.890 rows=37,294 loops=1)

8. 119,083.574 119,418.243 ↓ 1,967.4 13,772 1

Nested Loop (cost=1,263.45..7,834.73 rows=7 width=7) (actual time=26.969..119,418.243 rows=13,772 loops=1)

  • Join Filter: ((region.geom ~ aoi.center) AND _st_contains(region.geom, aoi.center))
  • Rows Removed by Join Filter: 1227904
9. 60.438 242.693 ↓ 62.6 45,988 1

Merge Join (cost=1,263.45..2,574.46 rows=735 width=39) (actual time=21.509..242.693 rows=45,988 loops=1)

  • Merge Cond: (aoi.user_id = proagrica_user.id)
10. 136.291 136.291 ↑ 1.3 73,221 1

Index Scan using proagrica_areaofinterest_user_id_4fcfdaec on proagrica_areaofinterest aoi (cost=0.43..78,256.96 rows=95,582 width=43) (actual time=0.007..136.291 rows=73,221 loops=1)

  • Index Cond: (user_id IS NOT NULL)
  • Filter: (NOT is_removed)
  • Rows Removed by Filter: 21772
11. 32.549 45.964 ↓ 5.3 48,135 1

Sort (cost=1,263.02..1,285.78 rows=9,106 width=4) (actual time=18.483..45.964 rows=48,135 loops=1)

  • Sort Key: proagrica_user.id
  • Sort Method: quicksort Memory: 1620kB
12. 12.923 13.415 ↓ 2.0 18,169 1

Seq Scan on proagrica_user (cost=51.51..664.18 rows=9,106 width=4) (actual time=1.002..13.415 rows=18,169 loops=1)

  • Filter: ((NOT (hashed SubPlan 1)) AND ((email)::text !~~ '%@eosda.com'::text))
  • Rows Removed by Filter: 852
13.          

SubPlan (for Seq Scan)

14. 0.492 0.492 ↑ 1.0 2,029 1

Seq Scan on test_users (cost=0.00..46.44 rows=2,029 width=4) (actual time=0.006..0.492 rows=2,029 loops=1)

15. 91.963 91.976 ↑ 1.0 27 45,988

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

16. 0.013 0.013 ↑ 1.0 27 1

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

17. 82.632 82.632 ↑ 8.5 2 13,772

Index Scan using proagrica_aoiyeardata_aoi_id_aa9a0631 on proagrica_aoiyeardata (cost=0.43..12.83 rows=17 width=12) (actual time=0.006..0.006 rows=2 loops=13,772)

  • Index Cond: (aoi_id = aoi.id)
Planning time : 0.552 ms
Execution time : 119,582.612 ms