explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ssa7

Settings
# exclusive inclusive rows x rows loops node
1. 1.636 3,317.349 ↓ 7.2 1,468 1

HashAggregate (cost=18,818.40..18,820.44 rows=204 width=98) (actual time=3,316.874..3,317.349 rows=1,468 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
2.          

CTE animais_aptos

3. 3,067.225 3,067.225 ↓ 1,028.4 5,142 1

Function Scan on obtenha_animais_estoque_por_contrato_propriedade (cost=0.25..12.75 rows=5 width=4) (actual time=3,059.770..3,067.225 rows=5,142 loops=1)

  • Filter: (propriedade_id = 3144)
  • Rows Removed by Filter: 36360
4.          

CTE novilhas

5. 3.586 3,307.957 ↓ 7.2 1,468 1

HashAggregate (cost=17,031.87..17,033.91 rows=204 width=11) (actual time=3,307.295..3,307.957 rows=1,468 loops=1)

  • Group Key: a.id, a.identificacao_usual, ('novilha'::text)
6. 0.791 3,304.371 ↓ 21.4 4,373 1

Append (cost=48.67..17,030.34 rows=204 width=11) (actual time=4.410..3,304.371 rows=4,373 loops=1)

7. 7.879 166.050 ↓ 17.7 3,570 1

Nested Loop Anti Join (cost=48.67..16,926.73 rows=202 width=11) (actual time=4.408..166.050 rows=3,570 loops=1)

8. 6.562 96.893 ↓ 101.1 20,426 1

Nested Loop (cost=48.11..15,174.89 rows=202 width=11) (actual time=1.620..96.893 rows=20,426 loops=1)

9. 15.226 16.571 ↓ 12.1 3,688 1

Bitmap Heap Scan on mbw_animal a (cost=47.55..7,635.52 rows=306 width=11) (actual time=1.485..16.571 rows=3,688 loops=1)

  • Recheck 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))
  • Rows Removed by Filter: 4220
  • Heap Blocks: exact=662
10. 1.345 1.345 ↓ 3.9 7,908 1

Bitmap Index Scan on mbw_animal_62d4a177 (cost=0.00..47.48 rows=2,006 width=0) (actual time=1.345..1.345 rows=7,908 loops=1)

  • Index Cond: (propriedade_id = 3144)
11. 73.760 73.760 ↓ 1.5 6 3,688

Index Scan using vw_modelo_reprodutivo_data_hora_cobertura_idx on vw_modelo_reprodutivo cb (cost=0.56..24.60 rows=4 width=4) (actual time=0.006..0.020 rows=6 loops=3,688)

  • Index Cond: ((femea_id = a.id) AND (data_hora_cobertura < '2018-07-01 00:00:00-03'::timestamp with time zone))
  • Filter: (cobertura_situacao_nome <> ALL ('{Servida,Gestante}'::text[]))
  • Rows Removed by Filter: 0
12. 61.278 61.278 ↑ 1.0 1 20,426

Index Scan using vw_modelo_reprodutivo_data_hora_cobertura_idx on vw_modelo_reprodutivo m (cost=0.56..8.63 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=20,426)

  • Index Cond: ((femea_id = a.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[])))
13. 1.104 3,113.005 ↓ 757.0 757 1

Nested Loop Anti Join (cost=0.99..50.28 rows=1 width=11) (actual time=3,059.860..3,113.005 rows=757 loops=1)

14. 1.526 3,096.553 ↓ 3,837.0 3,837 1

Nested Loop (cost=0.43..42.55 rows=1 width=11) (actual time=3,059.836..3,096.553 rows=3,837 loops=1)

15. 3,069.317 3,069.317 ↓ 1,028.4 5,142 1

CTE Scan on animais_aptos apt (cost=0.00..0.10 rows=5 width=4) (actual time=3,059.775..3,069.317 rows=5,142 loops=1)

16. 25.710 25.710 ↑ 1.0 1 5,142

Index Scan using mbw_animal_pkey on mbw_animal a_1 (cost=0.43..8.48 rows=1 width=11) (actual time=0.004..0.005 rows=1 loops=5,142)

  • Index Cond: (id = apt.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))
  • Rows Removed by Filter: 0
17. 15.348 15.348 ↑ 1.0 1 3,837

Index Only Scan using vw_modelo_reprodutivo_data_hora_cobertura_idx on vw_modelo_reprodutivo (cost=0.56..4.14 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=3,837)

  • 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) AND (contrato_id = 16))
  • Heap Fetches: 0
18. 0.134 24.525 ↓ 46.0 46 1

Nested Loop Anti Join (cost=0.99..51.29 rows=1 width=11) (actual time=20.736..24.525 rows=46 loops=1)

19. 0.691 22.559 ↓ 229.0 229 1

Nested Loop (cost=0.43..42.56 rows=1 width=11) (actual time=0.136..22.559 rows=229 loops=1)

20. 1.300 1.300 ↓ 1,028.4 5,142 1

CTE Scan on animais_aptos apt_1 (cost=0.00..0.10 rows=5 width=4) (actual time=0.001..1.300 rows=5,142 loops=1)

21. 20.568 20.568 ↓ 0.0 0 5,142

Index Scan using mbw_animal_pkey on mbw_animal a_2 (cost=0.43..8.48 rows=1 width=11) (actual time=0.004..0.004 rows=0 loops=5,142)

  • Index Cond: (id = apt_1.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))
  • Rows Removed by Filter: 1
22. 1.832 1.832 ↑ 1.0 1 229

Index Only Scan using vw_modelo_reprodutivo_data_hora_cobertura_idx on vw_modelo_reprodutivo vw_modelo_reprodutivo_1 (cost=0.56..4.64 rows=1 width=4) (actual time=0.008..0.008 rows=1 loops=229)

  • 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
23. 0.978 3,315.713 ↓ 7.2 1,468 1

Nested Loop Left Join (cost=0.56..1,769.19 rows=204 width=98) (actual time=3,307.311..3,315.713 rows=1,468 loops=1)

24. 3,308.863 3,308.863 ↓ 7.2 1,468 1

CTE Scan on novilhas n (cost=0.00..4.08 rows=204 width=94) (actual time=3,307.299..3,308.863 rows=1,468 loops=1)

25. 5.872 5.872 ↓ 0.0 0 1,468

Index Scan using vw_modelo_reprodutivo_data_hora_cobertura_idx on vw_modelo_reprodutivo vw (cost=0.56..8.64 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=1,468)

  • 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))
Planning time : 3.001 ms
Execution time : 3,327.426 ms