explain.depesz.com

PostgreSQL's explain analyze made readable

Result: o6m5

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 88.105 ↓ 0.0 0 1

HashAggregate (cost=27,027.51..27,043.11 rows=1,040 width=505) (actual time=88.105..88.105 rows=0 loops=1)

  • Group Key: l.cobertura_id, a.identificacao_usual, a.id, l.macho_raca_nome, l.cobertura_situacao_nome, l.tipo_cobertura, l.data_hora_cobertura, l.resultado_parto_nome, l.situacao_cria, l.parto_id, l.data_parto, l.propriedade_id, l.dg_inicial, (l.data_hora_cobertura + '275 days'::interval day), (l.data_hora_cobertura + '315 days'::interval day), c.sexo, p.propriedade_id, c.id, CASE WHEN ((r.identificacao_usual IS NOT NULL) OR (apt.id_animal IS NULL)) THEN true ELSE false END, CASE WHEN (apt.id_animal IS NULL) THEN 'saida-outros'::text ELSE r.tipo_descarte END, CASE WHEN (l.femea_id IS NOT NULL) THEN true ELSE false END, a.categoria, a.novilhas_chance
2.          

CTE animais_aptos

3. 3.037 3.037 ↓ 0.0 0 1

Function Scan on obtenha_animais_estoque_por_contrato_propriedade (cost=0.25..12.75 rows=5 width=4) (actual time=3.037..3.037 rows=0 loops=1)

  • Filter: (propriedade_id = 3144)
4.          

CTE novilhas

5. 0.001 3.063 ↓ 0.0 0 1

HashAggregate (cost=2,673.58..2,673.91 rows=33 width=11) (actual time=3.063..3.063 rows=0 loops=1)

  • Group Key: a_1.id, a_1.identificacao_usual, ('novilha'::text)
6. 0.000 3.062 ↓ 0.0 0 1

Append (cost=1.28..2,673.33 rows=33 width=11) (actual time=3.062..3.062 rows=0 loops=1)

7. 0.001 0.022 ↓ 0.0 0 1

Nested Loop Anti Join (cost=1.28..2,570.91 rows=31 width=11) (actual time=0.022..0.022 rows=0 loops=1)

8. 0.001 0.021 ↓ 0.0 0 1

Nested Loop (cost=0.85..2,299.26 rows=31 width=11) (actual time=0.021..0.021 rows=0 loops=1)

9. 0.020 0.020 ↓ 0.0 0 1

Index Scan using mbw_animal_62d4a177 on mbw_animal a_1 (cost=0.43..974.54 rows=54 width=11) (actual time=0.020..0.020 rows=0 loops=1)

  • Index Cond: (propriedade_id = 3144)
  • Filter: ((data_morte IS NULL) AND ((data_descarte_reprodutor IS NULL) OR (data_descarte_reprodutor < '2018-07-01'::date)) AND (tipo_id <> 4) AND (sexo = 0) AND (((date_part('year'::text, age('2018-07-01 00:00:00'::timestamp without time zone, (data_nascimento)::timestamp without time zone)) * '12'::double precision) + date_part('month'::text, age('2018-07-01 00:00:00'::timestamp without time zone, (data_nascimento)::timestamp without time zone))) > '16'::double precision))
10. 0.000 0.000 ↓ 0.0 0

Index Scan using vw_modelo_reprodutivo_data_hora_cobertura_idx on vw_modelo_reprodutivo cb (cost=0.43..24.48 rows=5 width=4) (never executed)

  • Index Cond: ((femea_id = a_1.id) AND (data_hora_cobertura < '2018-07-01 00:00:00-03'::timestamp with time zone))
  • Filter: (cobertura_situacao_nome <> ALL ('{Servida,Gestante}'::text[]))
11. 0.000 0.000 ↓ 0.0 0

Index Scan using vw_modelo_reprodutivo_data_hora_cobertura_idx on vw_modelo_reprodutivo m (cost=0.43..8.50 rows=1 width=4) (never executed)

  • Index Cond: ((femea_id = a_1.id) AND (data_hora_cobertura < '2018-07-01 00:00:00-03'::timestamp with time zone) AND (propriedade_id = 3144))
  • Filter: ((data_parto IS NOT NULL) AND (situacao_cria = ANY ('{Vivo,Descartado}'::text[])))
12. 0.000 3.039 ↓ 0.0 0 1

Nested Loop Anti Join (cost=0.85..50.95 rows=1 width=11) (actual time=3.039..3.039 rows=0 loops=1)

13. 0.001 3.039 ↓ 0.0 0 1

Nested Loop (cost=0.43..42.52 rows=1 width=11) (actual time=3.039..3.039 rows=0 loops=1)

14. 3.038 3.038 ↓ 0.0 0 1

CTE Scan on animais_aptos apt_1 (cost=0.00..0.10 rows=5 width=4) (actual time=3.038..3.038 rows=0 loops=1)

15. 0.000 0.000 ↓ 0.0 0

Index Scan using mbw_animal_pkey on mbw_animal a_2 (cost=0.43..8.47 rows=1 width=11) (never executed)

  • Index Cond: (id = apt_1.id_animal)
  • Filter: ((tipo_id <> 4) AND (contrato_id = 16) AND (sexo = 0) AND (((date_part('year'::text, age('2018-07-01 00:00:00'::timestamp without time zone, (data_nascimento)::timestamp without time zone)) * '12'::double precision) + date_part('month'::text, age('2018-07-01 00:00:00'::timestamp without time zone, (data_nascimento)::timestamp without time zone))) > '16'::double precision))
16. 0.000 0.000 ↓ 0.0 0

Index Only Scan using vw_modelo_reprodutivo_data_hora_cobertura_idx on vw_modelo_reprodutivo (cost=0.43..4.43 rows=1 width=4) (never executed)

  • Index Cond: ((femea_id = a_2.id) AND (data_hora_cobertura < '2018-07-01 00:00:00-03'::timestamp with time zone) AND (propriedade_id = 3144) AND (contrato_id = 16))
  • Heap Fetches: 0
17. 0.000 0.001 ↓ 0.0 0 1

Nested Loop Anti Join (cost=0.85..51.13 rows=1 width=11) (actual time=0.001..0.001 rows=0 loops=1)

18. 0.001 0.001 ↓ 0.0 0 1

Nested Loop (cost=0.43..42.54 rows=1 width=11) (actual time=0.001..0.001 rows=0 loops=1)

19. 0.000 0.000 ↓ 0.0 0 1

CTE Scan on animais_aptos apt_2 (cost=0.00..0.10 rows=5 width=4) (actual time=0.000..0.000 rows=0 loops=1)

20. 0.000 0.000 ↓ 0.0 0

Index Scan using mbw_animal_pkey on mbw_animal a_3 (cost=0.43..8.48 rows=1 width=11) (never executed)

  • Index Cond: (id = apt_2.id_animal)
  • Filter: ((data_descarte_reprodutor >= '2018-07-01'::date) AND (contrato_id = 16) AND (sexo = 0) AND (tipo_id = 4) AND (((date_part('year'::text, age('2018-07-01 00:00:00'::timestamp without time zone, (data_nascimento)::timestamp without time zone)) * '12'::double precision) + date_part('month'::text, age('2018-07-01 00:00:00'::timestamp without time zone, (data_nascimento)::timestamp without time zone))) > '16'::double precision))
21. 0.000 0.000 ↓ 0.0 0

Index Only Scan using vw_modelo_reprodutivo_data_hora_cobertura_idx on vw_modelo_reprodutivo vw_modelo_reprodutivo_1 (cost=0.43..4.51 rows=1 width=4) (never executed)

  • Index Cond: ((femea_id = a_3.id) AND (data_hora_cobertura < '2018-07-01 00:00:00-03'::timestamp with time zone) AND (propriedade_id = 3144) AND (contrato_id = 16))
  • Heap Fetches: 0
22.          

CTE retirada_em

23. 0.003 9.723 ↓ 0.0 0 1

HashAggregate (cost=6,691.97..6,692.12 rows=15 width=11) (actual time=9.723..9.723 rows=0 loops=1)

  • Group Key: a_4.id, a_4.identificacao_usual, ('Mortes'::text)
24. 0.003 9.720 ↓ 0.0 0 1

Append (cost=0.72..6,691.86 rows=15 width=11) (actual time=9.720..9.720 rows=0 loops=1)

25. 0.000 1.774 ↓ 0.0 0 1

Nested Loop (cost=0.72..1,151.29 rows=7 width=11) (actual time=1.774..1.774 rows=0 loops=1)

26. 1.774 1.774 ↓ 0.0 0 1

Index Scan using mbw_morte_868819a8 on mbw_morte m_1 (cost=0.29..314.00 rows=99 width=4) (actual time=1.774..1.774 rows=0 loops=1)

  • Index Cond: (contrato_id = 16)
  • Filter: (tipo_id <> 98)
27. 0.000 0.000 ↓ 0.0 0

Index Scan using mbw_animal_pkey on mbw_animal a_4 (cost=0.43..8.45 rows=1 width=11) (never executed)

  • Index Cond: (id = m_1.animal_id)
  • Filter: (data_morte <= '2019-03-31'::date)
28. 0.011 0.011 ↓ 0.0 0 1

Index Scan using mbw_animal_62d4a177 on mbw_animal a_5 (cost=0.43..966.84 rows=2 width=11) (actual time=0.011..0.011 rows=0 loops=1)

  • Index Cond: (propriedade_id = 3144)
  • Filter: ((data_descarte_reprodutor <= '2019-03-31'::date) AND (situacao_id <> 4))
29. 0.001 1.420 ↓ 0.0 0 1

Nested Loop (cost=16.91..159.82 rows=4 width=11) (actual time=1.420..1.420 rows=0 loops=1)

30. 0.000 1.419 ↓ 0.0 0 1

Nested Loop (cost=16.48..155.56 rows=4 width=4) (actual time=1.419..1.419 rows=0 loops=1)

31. 0.003 1.419 ↓ 0.0 0 1

Bitmap Heap Scan on mbw_venda v (cost=11.79..15.81 rows=1 width=4) (actual time=1.419..1.419 rows=0 loops=1)

  • Recheck Cond: ((propriedade_id = 3144) AND (contrato_id = 16))
  • Filter: ((tipo_venda_id <> 5) AND (data <= '2019-03-31'::date))
32. 0.000 1.416 ↓ 0.0 0 1

BitmapAnd (cost=11.79..11.79 rows=1 width=0) (actual time=1.416..1.416 rows=0 loops=1)

33. 1.416 1.416 ↓ 0.0 0 1

Bitmap Index Scan on mbw_venda_62d4a177 (cost=0.00..5.21 rows=106 width=0) (actual time=1.416..1.416 rows=0 loops=1)

  • Index Cond: (propriedade_id = 3144)
34. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on mbw_venda_868819a8 (cost=0.00..6.32 rows=254 width=0) (never executed)

  • Index Cond: (contrato_id = 16)
35. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on mbw_vendaitens vi (cost=4.70..139.41 rows=35 width=8) (never executed)

  • Recheck Cond: (venda_id = v.id)
36. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on mbw_vendaitens_2f2a853d (cost=0.00..4.69 rows=35 width=0) (never executed)

  • Index Cond: (venda_id = v.id)
37. 0.000 0.000 ↓ 0.0 0

Index Scan using mbw_animal_pkey on mbw_animal a_6 (cost=0.43..1.05 rows=1 width=11) (never executed)

  • Index Cond: (id = vi.animal_id)
38. 0.001 3.486 ↓ 0.0 0 1

Nested Loop (cost=26.22..913.10 rows=1 width=11) (actual time=3.486..3.486 rows=0 loops=1)

39. 0.019 3.485 ↓ 0.0 0 1

Nested Loop (cost=25.79..905.00 rows=1 width=4) (actual time=3.485..3.485 rows=0 loops=1)

40. 0.028 0.028 ↑ 9.3 3 1

Index Scan using mbw_venda_62d4a177 on mbw_venda v_1 (cost=0.42..81.77 rows=28 width=4) (actual time=0.018..0.028 rows=3 loops=1)

  • Index Cond: (propriedade_id = 31557)
  • Filter: ((data <= '2019-03-31'::date) AND (tipo_venda_id = 5))
  • Rows Removed by Filter: 5
41. 0.003 3.438 ↓ 0.0 0 3

Bitmap Heap Scan on mbw_vendaitens vi_1 (cost=25.38..29.39 rows=1 width=8) (actual time=1.146..1.146 rows=0 loops=3)

  • Recheck Cond: ((venda_id = v_1.id) AND (contrato_id = 16))
42. 0.003 3.435 ↓ 0.0 0 3

BitmapAnd (cost=25.38..25.38 rows=1 width=0) (actual time=1.145..1.145 rows=0 loops=3)

43. 0.015 0.015 ↑ 35.0 1 3

Bitmap Index Scan on mbw_vendaitens_2f2a853d (cost=0.00..4.69 rows=35 width=0) (actual time=0.005..0.005 rows=1 loops=3)

  • Index Cond: (venda_id = v_1.id)
44. 3.417 3.417 ↓ 0.0 0 3

Bitmap Index Scan on mbw_vendaitens_868819a8 (cost=0.00..20.18 rows=1,034 width=0) (actual time=1.139..1.139 rows=0 loops=3)

  • Index Cond: (contrato_id = 16)
45. 0.000 0.000 ↓ 0.0 0

Index Scan using mbw_animal_pkey on mbw_animal a_7 (cost=0.43..8.09 rows=1 width=11) (never executed)

  • Index Cond: (id = vi_1.animal_id)
46. 0.002 3.026 ↓ 0.0 0 1

Nested Loop (cost=7.15..3,500.67 rows=1 width=11) (actual time=3.026..3.026 rows=0 loops=1)

47. 0.008 3.024 ↓ 0.0 0 1

Bitmap Heap Scan on mbw_transferenciapropriedade t (cost=6.72..1,025.25 rows=296 width=4) (actual time=3.024..3.024 rows=0 loops=1)

  • Recheck Cond: (propriedade_origem_id = 3144)
  • Filter: (data <= '2019-03-31'::date)
48. 3.016 3.016 ↓ 0.0 0 1

Bitmap Index Scan on mbw_transferenciapropriedade_f8de2aa4 (cost=0.00..6.65 rows=297 width=0) (actual time=3.016..3.016 rows=0 loops=1)

  • Index Cond: (propriedade_origem_id = 3144)
49. 0.000 0.000 ↓ 0.0 0

Index Scan using mbw_animal_pkey on mbw_animal a_8 (cost=0.43..8.35 rows=1 width=11) (never executed)

  • Index Cond: (id = t.animal_id)
  • Filter: (contrato_id = 16)
50.          

CTE animais_supostamente_aptos

51. 0.005 88.095 ↓ 0.0 0 1

HashAggregate (cost=8,840.74..8,851.14 rows=1,040 width=67) (actual time=88.095..88.095 rows=0 loops=1)

  • Group Key: n.id, n.identificacao_usual, n.categoria, (CASE WHEN (vw.cobertura_id IS NOT NULL) THEN true ELSE false END), (CASE WHEN (vw.cobertura_id IS NOT NULL) THEN vw.cobertura_id ELSE NULL::integer END)
52. 0.004 88.090 ↓ 0.0 0 1

Append (cost=282.31..8,827.74 rows=1,040 width=67) (actual time=88.090..88.090 rows=0 loops=1)

53. 0.001 3.065 ↓ 0.0 0 1

HashAggregate (cost=282.31..282.64 rows=33 width=98) (actual time=3.065..3.065 rows=0 loops=1)

  • Group Key: n.id, n.identificacao_usual, n.categoria, CASE WHEN (vw.cobertura_id IS NOT NULL) THEN true ELSE false END, CASE WHEN (vw.cobertura_id IS NOT NULL) THEN vw.cobertura_id ELSE NULL::integer END
54. 0.001 3.064 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.43..281.90 rows=33 width=98) (actual time=3.064..3.064 rows=0 loops=1)

55. 3.063 3.063 ↓ 0.0 0 1

CTE Scan on novilhas n (cost=0.00..0.66 rows=33 width=94) (actual time=3.063..3.063 rows=0 loops=1)

56. 0.000 0.000 ↓ 0.0 0

Index Scan using vw_modelo_reprodutivo_data_hora_cobertura_idx on vw_modelo_reprodutivo vw (cost=0.43..8.51 rows=1 width=8) (never executed)

  • Index Cond: ((femea_id = n.id) AND (data_hora_cobertura < '2018-07-01 00:00:00-03'::timestamp with time zone) AND (propriedade_id = 3144) AND (contrato_id = 16))
57. 5.758 5.758 ↓ 0.0 0 1

Function Scan on reprodutivo_partos_previstos (cost=0.25..10.25 rows=5 width=66) (actual time=5.758..5.758 rows=0 loops=1)

  • Filter: (data_parto IS NULL)
58. 0.002 9.726 ↓ 0.0 0 1

Nested Loop (cost=0.43..128.17 rows=1 width=15) (actual time=9.726..9.726 rows=0 loops=1)

59. 9.724 9.724 ↓ 0.0 0 1

CTE Scan on retirada_em r_1 (cost=0.00..0.30 rows=15 width=4) (actual time=9.724..9.724 rows=0 loops=1)

60. 0.000 0.000 ↓ 0.0 0

Index Scan using vw_modelo_reprodutivo_data_hora_cobertura_idx on vw_modelo_reprodutivo vw_1 (cost=0.43..8.51 rows=1 width=15) (never executed)

  • Index Cond: ((femea_id = r_1.id) AND (propriedade_id = 3144) AND (contrato_id = 16))
  • Filter: (cobertura_situacao_nome = 'Gestante'::text)
61. 0.001 64.780 ↓ 0.0 0 1

Nested Loop Anti Join (cost=1.10..8,386.02 rows=1 width=66) (actual time=64.780..64.780 rows=0 loops=1)

62. 0.002 64.779 ↓ 0.0 0 1

Nested Loop (cost=0.68..8,369.75 rows=1 width=66) (actual time=64.779..64.779 rows=0 loops=1)

63. 64.777 64.777 ↓ 0.0 0 1

Function Scan on reprodutivo_partos_perdas f (cost=0.25..10.25 rows=1,000 width=66) (actual time=64.777..64.777 rows=0 loops=1)

64. 0.000 0.000 ↓ 0.0 0

Index Scan using vw_modelo_reprodutivo_data_hora_cobertura_idx on vw_modelo_reprodutivo vw_2 (cost=0.43..8.35 rows=1 width=8) (never executed)

  • Index Cond: ((femea_id = f.femea_id) AND (propriedade_id = 3144) AND (contrato_id = 16))
  • Filter: ((categoria_femea <> 'Novilha'::text) AND (f.cobertura_id = cobertura_id))
65. 0.000 0.000 ↓ 0.0 0

Index Scan using vw_modelo_reprodutivo_data_hora_cobertura_idx on vw_modelo_reprodutivo v_2 (cost=0.43..8.35 rows=1 width=4) (never executed)

  • Index Cond: ((f.femea_id = femea_id) AND (propriedade_id = 3144) AND (contrato_id = 16))
  • Filter: (situacao_cria = 'Vivo'::text)
66. 4.757 4.757 ↓ 0.0 0 1

Function Scan on reprodutivo_partos_realizados f_1 (cost=0.25..10.25 rows=1,000 width=66) (actual time=4.757..4.757 rows=0 loops=1)

67.          

CTE last_cobertura

68. 0.000 0.000 ↓ 0.0 0

WindowAgg (cost=8,651.13..8,658.59 rows=373 width=72) (never executed)

69. 0.000 0.000 ↓ 0.0 0

Sort (cost=8,651.13..8,652.07 rows=373 width=72) (never executed)

  • Sort Key: a_9.id, vw_3.data_hora_cobertura DESC
70. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.43..8,635.20 rows=373 width=72) (never executed)

71. 0.000 0.000 ↓ 0.0 0

CTE Scan on animais_supostamente_aptos a_9 (cost=0.00..20.80 rows=1,040 width=4) (never executed)

72. 0.000 0.000 ↓ 0.0 0

Index Scan using vw_modelo_reprodutivo_data_hora_cobertura_idx on vw_modelo_reprodutivo vw_3 (cost=0.43..8.27 rows=1 width=68) (never executed)

  • Index Cond: ((femea_id = a_9.id) AND (data_hora_cobertura >= '2018-07-01 00:00:00-03'::timestamp with time zone) AND (data_hora_cobertura <= '2019-03-31 00:00:00-03'::timestamp with time zone))
73. 0.001 88.100 ↓ 0.0 0 1

Hash Left Join (cost=40.36..79.20 rows=1,040 width=505) (actual time=88.100..88.100 rows=0 loops=1)

  • Hash Cond: (a.id = apt.id_animal)
74. 0.001 88.099 ↓ 0.0 0 1

Hash Left Join (cost=40.19..69.67 rows=1,040 width=501) (actual time=88.099..88.099 rows=0 loops=1)

  • Hash Cond: ((a.identificacao_usual)::text = (r.identificacao_usual)::text)
75. 0.001 88.098 ↓ 0.0 0 1

Hash Left Join (cost=39.71..64.51 rows=1,040 width=411) (actual time=88.098..88.098 rows=0 loops=1)

  • Hash Cond: (a.id = l.femea_id)
76. 88.097 88.097 ↓ 0.0 0 1

CTE Scan on animais_supostamente_aptos a (cost=0.00..20.80 rows=1,040 width=95) (actual time=88.097..88.097 rows=0 loops=1)

77. 0.000 0.000 ↓ 0.0 0

Hash (cost=39.68..39.68 rows=2 width=316) (never executed)

78. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=1.27..39.68 rows=2 width=316) (never executed)

79. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=0.85..26.36 rows=2 width=314) (never executed)

80. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=0.42..25.30 rows=2 width=314) (never executed)

81. 0.000 0.000 ↓ 0.0 0

CTE Scan on last_cobertura l (cost=0.00..8.39 rows=2 width=306) (never executed)

  • Filter: (ordem = 1)
82. 0.000 0.000 ↓ 0.0 0

Index Scan using mbw_parto_pkey on mbw_parto p (cost=0.42..8.44 rows=1 width=8) (never executed)

  • Index Cond: (l.parto_id = id)
83. 0.000 0.000 ↓ 0.0 0

Index Scan using mbw_partocria_6e4499ea on mbw_partocria pc (cost=0.42..0.52 rows=1 width=8) (never executed)

  • Index Cond: (parto_id = p.id)
  • Filter: (situacao_cria = ANY ('{1,2}'::integer[]))
84. 0.000 0.000 ↓ 0.0 0

Index Scan using mbw_animal_pkey on mbw_animal c (cost=0.43..6.65 rows=1 width=6) (never executed)

  • Index Cond: (id = pc.cria_id)
85. 0.000 0.000 ↓ 0.0 0

Hash (cost=0.30..0.30 rows=15 width=90) (never executed)

86. 0.000 0.000 ↓ 0.0 0

CTE Scan on retirada_em r (cost=0.00..0.30 rows=15 width=90) (never executed)

87. 0.000 0.000 ↓ 0.0 0

Hash (cost=0.10..0.10 rows=5 width=4) (never executed)

88. 0.000 0.000 ↓ 0.0 0

CTE Scan on animais_aptos apt (cost=0.00..0.10 rows=5 width=4) (never executed)

Planning time : 6.761 ms
Execution time : 89.432 ms