explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wR9w : temp

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Insert on cagiltmap (cost=2,027.91..2,027.93 rows=1 width=339) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* (cost=2,027.91..2,027.93 rows=1 width=339) (actual rows= loops=)

3. 0.000 0.000 ↓ 0.0

Sort (cost=2,027.91..2,027.91 rows=1 width=141) (actual rows= loops=)

  • Sort Key: ((at.incoming)::timestamp with time zone), ad.primary_identification, ad.primary_identification_index
4. 0.000 0.000 ↓ 0.0

Nested Loop (cost=771.36..2,027.90 rows=1 width=141) (actual rows= loops=)

  • Join Filter: (at.incoming <= (max(fc3.calendar_date)))
5. 0.000 0.000 ↓ 0.0

Nested Loop (cost=770.79..2,010.00 rows=1 width=251) (actual rows= loops=)

  • Join Filter: (at.incoming >= (min(fc1.calendar_date)))
6. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=770.22..1,992.18 rows=1 width=251) (actual rows= loops=)

  • Join Filter: (ad.farm_id = heat_calendar.farm_id)
7. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=769.93..1,991.86 rows=1 width=251) (actual rows= loops=)

  • Join Filter: (ad.farm_id = incoming_calendar.farm_id)
8. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=769.64..1,991.44 rows=1 width=243) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=769.51..1,991.28 rows=1 width=133) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=769.09..1,989.91 rows=1 width=125) (actual rows= loops=)

  • Join Filter: ((ad.farm_id = loss.farm_id) AND (ad.animal_id = loss.animal_id))
11. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=768.67..1,987.05 rows=1 width=109) (actual rows= loops=)

  • Join Filter: ((ad.farm_id = weaning.farm_id) AND (ad.animal_id = weaning.animal_id) AND (weaning.cycle = farrowing.cycle))
12. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=768.67..1,230.34 rows=1 width=105) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=768.38..1,230.01 rows=1 width=89) (actual rows= loops=)

  • Join Filter: (farrowing.animal_event_reference_id = first_mating.id)
14. 0.000 0.000 ↓ 0.0

Nested Loop (cost=767.96..1,228.61 rows=1 width=77) (actual rows= loops=)

  • Join Filter: (ad.animal_id = a.id)
15. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=767.53..1,226.18 rows=2 width=77) (actual rows= loops=)

  • Join Filter: ((ad.farm_id = first_mating.farm_id) AND (ad.animal_id = first_mating.animal_id))
16. 0.000 0.000 ↓ 0.0

Nested Loop (cost=767.53..1,014.67 rows=2 width=65) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Nested Loop (cost=767.24..1,008.37 rows=20 width=65) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=766.82..993.86 rows=11 width=49) (actual rows= loops=)

  • Hash Cond: (heat_dates.animal_id = ad.animal_id)
19. 0.000 0.000 ↓ 0.0

Seq Scan on cafirstandlastheatdate heat_dates (cost=0.00..183.60 rows=11,560 width=20) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Hash (cost=766.68..766.68 rows=11 width=33) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Nested Loop (cost=542.63..766.68 rows=11 width=33) (actual rows= loops=)

  • Join Filter: (at.animal_id = ad.animal_id)
22. 0.000 0.000 ↓ 0.0

Hash Join (cost=542.35..762.93 rows=11 width=16) (actual rows= loops=)

  • Hash Cond: (tmp.animal_id = at.animal_id)
23. 0.000 0.000 ↓ 0.0

Seq Scan on caanimalidtmp tmp (cost=0.00..175.07 rows=12,107 width=4) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Hash (cost=529.90..529.90 rows=996 width=12) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Index Scan using idx_animaltracking_2 on animaltracking at (cost=0.43..529.90 rows=996 width=12) (actual rows= loops=)

  • Index Cond: (farm_id = 6854)
26. 0.000 0.000 ↓ 0.0

Index Scan using idx_animaldetail_1 on animaldetail ad (cost=0.29..0.33 rows=1 width=21) (actual rows= loops=)

  • Index Cond: (animal_id = tmp.animal_id)
  • Filter: (farm_id = 6854)
27. 0.000 0.000 ↓ 0.0

Index Scan using idx_animalevent_1 on animalevent heat (cost=0.42..1.30 rows=2 width=20) (actual rows= loops=)

  • Index Cond: (animal_id = ad.animal_id)
  • Filter: ((farm_id = 6854) AND (animal_event_type_id = 13))
28. 0.000 0.000 ↓ 0.0

Index Scan using idx_heatdetail_1 on heatdetail heat_detail (cost=0.29..0.32 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (animal_event_id = heat.id)
  • Filter: (heat IS NOT NULL)
29. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..209.66 rows=57 width=20) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Seq Scan on cafirstmating first_mating (cost=0.00..209.38 rows=57 width=20) (actual rows= loops=)

  • Filter: (farm_id = 6854)
31. 0.000 0.000 ↓ 0.0

Index Scan using animal_pkey on animal a (cost=0.43..1.20 rows=1 width=12) (actual rows= loops=)

  • Index Cond: (id = heat.animal_id)
32. 0.000 0.000 ↓ 0.0

Index Scan using idx_animalevent_1 on animalevent farrowing (cost=0.42..1.36 rows=4 width=24) (actual rows= loops=)

  • Index Cond: (ad.animal_id = animal_id)
  • Filter: ((farm_id = 6854) AND (animal_event_type_id = 11) AND (ad.farm_id = farm_id))
33. 0.000 0.000 ↓ 0.0

Index Scan using idx_farrowingdetail_1 on farrowingdetail fd (cost=0.29..0.32 rows=1 width=20) (actual rows= loops=)

  • Index Cond: (animal_event_id = farrowing.id)
34. 0.000 0.000 ↓ 0.0

Seq Scan on catotalweanedpigletsbycycle weaning (cost=0.00..753.12 rows=205 width=20) (actual rows= loops=)

  • Filter: (farm_id = 6854)
35. 0.000 0.000 ↓ 0.0

Index Scan using idx_animalevent_5 on animalevent loss (cost=0.42..2.85 rows=1 width=28) (actual rows= loops=)

  • Index Cond: (animal_event_reference_id = first_mating.id)
  • Filter: ((farm_id = 6854) AND is_reproductive_loss(animal_event_type_id))
36. 0.000 0.000 ↓ 0.0

Index Scan using idx_animalevent_1 on animalevent finishing (cost=0.42..1.36 rows=1 width=20) (actual rows= loops=)

  • Index Cond: (ad.animal_id = animal_id)
  • Filter: ((animal_event_type_id = ANY ('{8,15}'::integer[])) AND (farm_id = 6854) AND (ad.farm_id = farm_id))
37. 0.000 0.000 ↓ 0.0

Index Scan using animaleventtype_pkey on animaleventtype aet (cost=0.14..0.16 rows=1 width=122) (actual rows= loops=)

  • Index Cond: (COALESCE(loss.animal_event_type_id, finishing.animal_event_type_id) = id)
38. 0.000 0.000 ↓ 0.0

Index Scan using idx_farmcalendar_2 on farmcalendar incoming_calendar (cost=0.29..0.41 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (at.incoming = calendar_date)
  • Filter: (farm_id = 6854)
39. 0.000 0.000 ↓ 0.0

Index Scan using idx_farmcalendar_2 on farmcalendar heat_calendar (cost=0.29..0.32 rows=1 width=12) (actual rows= loops=)

  • Index Cond: ((heat_dates.first_heat_date)::date = calendar_date)
  • Filter: (farm_id = 6854)
40. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=0.57..17.80 rows=1 width=8) (actual rows= loops=)

  • Group Key: fc1.farm_id
41. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.57..17.75 rows=7 width=8) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Index Scan using idx_farmcalendar_2 on farmcalendar fc2 (cost=0.29..2.51 rows=1 width=12) (actual rows= loops=)

  • Index Cond: (calendar_date = '2019-01-01'::date)
  • Filter: (farm_id = 6854)
43. 0.000 0.000 ↓ 0.0

Index Scan using idx_farmcalendar_4 on farmcalendar fc1 (cost=0.29..15.18 rows=7 width=16) (actual rows= loops=)

  • Index Cond: (year = fc2.year)
  • Filter: ((farm_id = 6854) AND (fc2.week = week))
44. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=0.57..17.80 rows=1 width=8) (actual rows= loops=)

  • Group Key: fc3.farm_id
45. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.57..17.75 rows=7 width=8) (actual rows= loops=)

46. 0.000 0.000 ↓ 0.0

Index Scan using idx_farmcalendar_2 on farmcalendar fc4 (cost=0.29..2.51 rows=1 width=12) (actual rows= loops=)

  • Index Cond: (calendar_date = '2019-12-31'::date)
  • Filter: (farm_id = 6854)
47. 0.000 0.000 ↓ 0.0

Index Scan using idx_farmcalendar_4 on farmcalendar fc3 (cost=0.29..15.18 rows=7 width=16) (actual rows= loops=)

  • Index Cond: (year = fc4.year)
  • Filter: ((farm_id = 6854) AND (fc4.week = week)