explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jivp

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

Hash Join (cost=637,798.64..637,836.76 rows=5 width=788) (actual rows= loops=)

  • Hash Cond: (f_em.usar_no_join = r.usar_no_join)
2.          

CTE ult_cb

3. 0.000 0.000 ↓ 0.0

WindowAgg (cost=364,908.70..364,952.90 rows=2,210 width=47) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Sort (cost=364,908.70..364,914.23 rows=2,210 width=35) (actual rows= loops=)

  • Sort Key: vw.femea_id, vw.data_hora_cobertura DESC
5. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.99..364,785.94 rows=2,210 width=35) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Index Scan using vw_modelo_reprodutivo_parto_id_idx on vw_modelo_reprodutivo vw (cost=0.56..358,949.61 rows=2,231 width=35) (actual rows= loops=)

  • Index Cond: (contrato_id = 16)
  • Filter: ((((data_hora_cobertura + '270 days'::interval day) >= '2019-06-01'::date) AND ((data_hora_cobertura + '270 days'::interval day) <= '2020-02-29'::date)) OR (((data_hora_cobertura + '315 days'::interval day) >= '2019-06-01'::date) AND ((data_hora_cobertura + '315 days'::interval day) <= '2020-02-29'::date)))
7. 0.000 0.000 ↓ 0.0

Index Scan using mbw_animal_pkey on mbw_animal a (cost=0.43..2.62 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (id = vw.femea_id)
  • Filter: ((data_descarte_reprodutor IS NULL) OR (data_descarte_reprodutor > '2019-11-10'::date))
8.          

CTE fora_em

9. 0.000 0.000 ↓ 0.0

Function Scan on reprodutivo_femeas_fora_em (cost=0.25..10.25 rows=1,000 width=84) (actual rows= loops=)

10.          

CTE resultado

11. 0.000 0.000 ↓ 0.0

Aggregate (cost=272,835.44..272,835.45 rows=1 width=620) (actual rows= loops=)

12.          

CTE femeas_total

13. 0.000 0.000 ↓ 0.0

HashAggregate (cost=272,683.15..272,689.43 rows=628 width=462) (actual rows= loops=)

  • Group Key: "*SELECT* 1".cobertura_id, "*SELECT* 1".femea_identificacao_usual, "*SELECT* 1".femea_id, "*SELECT* 1".macho_raca_nome, "*SELECT* 1".cobertura_situacao_nome, "*SELECT* 1".tipo_cobertura, (("*SELECT* 1".data_hora_cobertura)::timestamp with time zone), "*SELECT* 1".resultado_parto_nome, "*SELECT* 1".situacao_cria, "*SELECT* 1".parto_id, "*SELECT* 1".data_parto, "*SELECT* 1".propriedade_id, "*SELECT* 1".dg_inicial, (("*SELECT* 1".previsao_parto_inicio)::timestamp with time zone), "*SELECT* 1".previsao_parto_fim, "*SELECT* 1".sexo_cria, "*SELECT* 1".propriedade_parto, "*SELECT* 1".cria_id, "*SELECT* 1".retirada_em, "*SELECT* 1".tipo_retirada, "*SELECT* 1".inseminda_em_atual, "*SELECT* 1".categoria_dasborad, "*SELECT* 1".novilha_segunda_chance, (false), "*SELECT* 1".propriedade_ult_cob
14. 0.000 0.000 ↓ 0.0

Append (cost=50.12..272,643.90 rows=628 width=462) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 1 (cost=50.12..72.17 rows=625 width=462) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=50.12..62.80 rows=625 width=462) (actual rows= loops=)

  • Hash Cond: (apto.femea_id = ult_cb.femea_id)
17. 0.000 0.000 ↓ 0.0

Function Scan on reprodutivo_femeas_aptas apto (cost=0.25..10.25 rows=625 width=457) (actual rows= loops=)

  • Filter: ((retirada_em IS FALSE) OR ((retirada_em IS TRUE) AND (inseminda_em_atual IS TRUE)))
18. 0.000 0.000 ↓ 0.0

Hash (cost=49.73..49.73 rows=11 width=8) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

CTE Scan on ult_cb (cost=0.00..49.73 rows=11 width=8) (actual rows= loops=)

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

Nested Loop Anti Join (cost=1.24..192,938.35 rows=2 width=173) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.81..192,918.65 rows=2 width=97) (actual rows= loops=)

  • Join Filter: (ult_cb_1.femea_id = vw_1.femea_id)
22. 0.000 0.000 ↓ 0.0

Nested Loop Anti Join (cost=0.81..192,868.54 rows=2 width=93) (actual rows= loops=)

  • Join Filter: (reprodutivo_femeas_aptas.femea_id = vw_1.femea_id)
23. 0.000 0.000 ↓ 0.0

Index Scan using vw_modelo_reprodutivo_parto_id_idx2 on vw_modelo_reprodutivo vw_1 (cost=0.56..192,778.29 rows=4 width=93) (actual rows= loops=)

  • Index Cond: (propriedade_id = 3162)
  • Filter: ((data_hora_cobertura >= '2019-11-10'::date) AND (data_hora_cobertura <= '2020-03-15'::date) AND (contrato_id = 16))
24. 0.000 0.000 ↓ 0.0

Function Scan on reprodutivo_femeas_aptas (cost=0.25..10.25 rows=1,000 width=4) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

CTE Scan on ult_cb ult_cb_1 (cost=0.00..49.73 rows=11 width=8) (actual rows= loops=)

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

Index Scan using vw_modelo_reprodutivo_femea_id_idx on vw_modelo_reprodutivo m (cost=0.43..6.71 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (femea_id = vw_1.femea_id)
  • Filter: ((data_parto IS NOT NULL) AND (data_hora_cobertura < '2019-11-10'::date) AND (situacao_cria <> ALL ('{Vivo,Descartado}'::text[])) AND (propriedade_id = 3162))
27. 0.000 0.000 ↓ 0.0

Nested Loop Anti Join (cost=79,607.63..79,630.21 rows=1 width=173) (actual rows= loops=)

  • Join Filter: (reprodutivo_femeas_aptas_1.femea_id = vw_2.femea_id)
28. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=79,607.38..79,607.45 rows=1 width=97) (actual rows= loops=)

  • Merge Cond: (vw_2.femea_id = ult_cb_2.femea_id)
29. 0.000 0.000 ↓ 0.0

Sort (cost=79,557.46..79,557.47 rows=1 width=93) (actual rows= loops=)

  • Sort Key: vw_2.femea_id
30. 0.000 0.000 ↓ 0.0

Nested Loop (cost=79,518.74..79,557.45 rows=1 width=93) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

HashAggregate (cost=79,518.18..79,518.32 rows=14 width=4) (actual rows= loops=)

  • Group Key: m_1.femea_id
32. 0.000 0.000 ↓ 0.0

Index Scan using vw_modelo_reprodutivo_parto_id_idx2 on vw_modelo_reprodutivo m_1 (cost=0.56..79,518.15 rows=14 width=4) (actual rows= loops=)

  • Index Cond: ((data_parto IS NOT NULL) AND (propriedade_id = 3162))
  • Filter: ((data_hora_cobertura < '2019-11-10'::date) AND (situacao_cria <> ALL ('{Vivo,Descartado}'::text[])))
33. 0.000 0.000 ↓ 0.0

Index Scan using vw_modelo_reprodutivo_data_hora_cobertura_idx on vw_modelo_reprodutivo vw_2 (cost=0.56..2.79 rows=1 width=93) (actual rows= loops=)

  • Index Cond: ((femea_id = m_1.femea_id) AND (data_hora_cobertura >= '2019-11-10'::date) AND (data_hora_cobertura <= '2020-03-15'::date) AND (propriedade_id = 3162) AND (contrato_id = 16))
34. 0.000 0.000 ↓ 0.0

Sort (cost=49.92..49.94 rows=11 width=8) (actual rows= loops=)

  • Sort Key: ult_cb_2.femea_id
35. 0.000 0.000 ↓ 0.0

CTE Scan on ult_cb ult_cb_2 (cost=0.00..49.73 rows=11 width=8) (actual rows= loops=)

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

Function Scan on reprodutivo_femeas_aptas reprodutivo_femeas_aptas_1 (cost=0.25..10.25 rows=1,000 width=4) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

CTE Scan on femeas_total (cost=0.00..12.56 rows=628 width=75) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

CTE Scan on fora_em f_em (cost=0.00..20.00 rows=1,000 width=84) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Hash (cost=0.02..0.02 rows=1 width=620) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

CTE Scan on resultado r (cost=0.00..0.02 rows=1 width=620) (actual rows= loops=)