explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QWxf

Settings
# exclusive inclusive rows x rows loops node
1. 33.040 6,142,566.066 ↓ 15,444.0 15,444 1

Sort (cost=879,666.06..879,666.07 rows=1 width=607) (actual time=6,142,563.441..6,142,566.066 rows=15,444 loops=1)

  • Sort Key: ranked_data.plot_id, ranked_data.pm_latitude, ranked_data.pm_longitude, ranked_data.crop_id, ranked_data.pest_id, ranked_data.task_type_id, ranked_data.pm_start_time
  • Sort Method: external merge Disk: 5728kB
  • Functions: 56
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 35.592 ms, Inlining 154.874 ms, Optimization 1121.279 ms, Emission 806.719 ms, Total 2118.464 ms
2. 3.237 6,142,533.026 ↓ 15,444.0 15,444 1

Subquery Scan on ranked_data (cost=879,666.01..879,666.05 rows=1 width=607) (actual time=6,142,466.333..6,142,533.026 rows=15,444 loops=1)

  • Filter: (ranked_data.rank = 1)
  • Rows Removed by Filter: 50301
3. 49.590 6,142,529.789 ↓ 65,745.0 65,745 1

WindowAgg (cost=879,666.01..879,666.04 rows=1 width=615) (actual time=6,142,466.325..6,142,529.789 rows=65,745 loops=1)

4. 434.788 6,142,480.199 ↓ 65,745.0 65,745 1

Sort (cost=879,666.01..879,666.02 rows=1 width=607) (actual time=6,142,453.072..6,142,480.199 rows=65,745 loops=1)

  • Sort Key: plot.id, pest.id, (date_trunc('day'::text, pm.start_time)) DESC
  • Sort Method: external merge Disk: 24048kB
5. 258,398.814 6,142,045.411 ↓ 65,745.0 65,745 1

Nested Loop Left Join (cost=1,111.26..879,666.00 rows=1 width=607) (actual time=22,819.577..6,142,045.411 rows=65,745 loops=1)

  • Join Filter: (action_file.action_id = pm.id)
  • Rows Removed by Join Filter: 5543593290
6. 162.204 391,769.512 ↓ 65,745.0 65,745 1

Nested Loop Left Join (cost=1,110.85..870,354.31 rows=1 width=535) (actual time=22,655.434..391,769.512 rows=65,745 loops=1)

7. 178.288 391,278.583 ↓ 65,745.0 65,745 1

Nested Loop (cost=1,110.57..870,347.20 rows=1 width=491) (actual time=22,654.684..391,278.583 rows=65,745 loops=1)

8. 213.238 390,705.825 ↓ 65,745.0 65,745 1

Nested Loop (cost=1,110.29..870,340.06 rows=1 width=491) (actual time=22,654.143..390,705.825 rows=65,745 loops=1)

9. 146.085 390,229.607 ↓ 65,745.0 65,745 1

Nested Loop (cost=1,110.00..870,332.75 rows=1 width=393) (actual time=22,653.669..390,229.607 rows=65,745 loops=1)

10. 143.472 389,820.542 ↓ 65,745.0 65,745 1

Nested Loop (cost=1,109.86..870,327.42 rows=1 width=401) (actual time=22,653.298..389,820.542 rows=65,745 loops=1)

11. 392.553 388,888.130 ↓ 65,745.0 65,745 1

Nested Loop Left Join (cost=1,109.57..870,319.99 rows=1 width=324) (actual time=22,653.082..388,888.130 rows=65,745 loops=1)

  • Filter: ((farm.id IS NULL) OR (farm.id = ANY ('{72155209181298692,72155209181298690,72155209181364233,72155209181298693,72155209181364230,72155209181298689,72155209181364231,72155209181364227,72155209181298691,72155209181364229,72155209181364226,72155209181364228,72155209181364232,72155209181364225,72158478521860097}'::bigint[])))
  • Rows Removed by Filter: 24642
12. 119.319 387,772.481 ↓ 179.7 90,387 1

Nested Loop (cost=1,109.28..866,569.27 rows=503 width=279) (actual time=22,652.478..387,772.481 rows=90,387 loops=1)

13. 2,106.546 2,106.546 ↓ 1.8 7 1

Index Scan using entity_type_pkey on entity_type pest (cost=0.29..56.78 rows=4 width=106) (actual time=2,104.295..2,106.546 rows=7 loops=1)

  • Index Cond: (id = ANY ('{72148059951005697,72155118885208065,72155118883110913,72155118887108609,72155118888943617,72155804903997441,72155804901638145}'::bigint[]))
  • Filter: (main_type = 3)
14. 384,973.351 385,546.616 ↓ 31.5 12,912 7

Bitmap Heap Scan on pest_measurement pm (cost=1,109.00..216,624.02 rows=410 width=181) (actual time=9,085.231..55,078.088 rows=12,912 loops=7)

  • Recheck Cond: (pest_id = pest.id)
  • Rows Removed by Index Recheck: 7745054
  • Filter: ((start_time >= '2018-12-29 00:00:00'::timestamp without time zone) AND (start_time < '2019-01-29 00:00:00'::timestamp without time zone) AND (project_id = '72154491368833025'::bigint) AND (plan_status = ANY ('{1,7,0}'::integer[])))
  • Rows Removed by Filter: 278278
  • Heap Blocks: exact=284640 lossy=1546102
15. 573.265 573.265 ↓ 4.9 291,190 7

Bitmap Index Scan on pest_measurement_pest_id_idx (cost=0.00..1,108.90 rows=59,777 width=0) (actual time=81.895..81.895 rows=291,190 loops=7)

  • Index Cond: (pest_id = pest.id)
16. 723.096 723.096 ↑ 1.0 1 90,387

Index Scan using location_pkey on location farm (cost=0.29..7.43 rows=1 width=53) (actual time=0.008..0.008 rows=1 loops=90,387)

  • Index Cond: (id = pm.owner_loc_id)
17. 788.940 788.940 ↑ 1.0 1 65,745

Index Scan using location_pkey on location plot (cost=0.29..7.43 rows=1 width=85) (actual time=0.012..0.012 rows=1 loops=65,745)

  • Index Cond: (id = pm.location_id)
18. 262.980 262.980 ↑ 1.0 1 65,745

Index Scan using location_type_pkey on location_type plot_type (cost=0.14..5.17 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=65,745)

  • Index Cond: (id = plot.type_id)
  • Filter: (main_type = 3)
19. 262.980 262.980 ↑ 1.0 1 65,745

Index Scan using entity_type_pkey on entity_type crop (cost=0.29..7.31 rows=1 width=106) (actual time=0.004..0.004 rows=1 loops=65,745)

  • Index Cond: (id = pm.crop_id)
20. 394.470 394.470 ↑ 1.0 1 65,745

Index Only Scan using task_type_pkey on task_type (cost=0.28..7.13 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=65,745)

  • Index Cond: (id = pm.type_id)
  • Heap Fetches: 65745
21. 328.725 328.725 ↑ 1.0 1 65,745

Index Scan using contact_info_pkey on contact_info (cost=0.28..7.10 rows=1 width=60) (actual time=0.005..0.005 rows=1 loops=65,745)

  • Index Cond: (id = pm.user_id)
22. 3,672,384.210 5,491,877.085 ↓ 1.2 84,320 65,745

GroupAggregate (cost=0.42..7,753.75 rows=69,242 width=80) (actual time=0.011..83.533 rows=84,320 loops=65,745)

  • Group Key: action_file.action_id
23. 1,819,492.875 1,819,492.875 ↑ 1.0 113,909 65,745

Index Scan using action_file_action_id_idx on action_file (cost=0.42..5,290.69 rows=113,954 width=95) (actual time=0.008..27.675 rows=113,909 loops=65,745)

Execution time : 6,142,608.569 ms