explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lqHC

Settings
# exclusive inclusive rows x rows loops node
1. 7.408 18,975.083 ↓ 7.2 1,468 1

HashAggregate (cost=15,865.33..15,867.38 rows=205 width=11) (actual time=18,974.578..18,975.083 rows=1,468 loops=1)

  • Group Key: a.id, a.identificacao_usual, ('novilha'::text)
2.          

CTE animais_aptos

3. 2,074.178 2,074.178 ↓ 1,028.4 5,142 1

Function Scan on obtenha_animais_estoque_por_contrato_teste (cost=0.25..12.75 rows=5 width=4) (actual time=2,068.746..2,074.178 rows=5,142 loops=1)

  • Filter: (propriedade_id = 3144)
  • Rows Removed by Filter: 36360
4. 1.231 18,967.675 ↓ 21.3 4,373 1

Append (cost=47.60..15,851.04 rows=205 width=11) (actual time=280.529..18,967.675 rows=4,373 loops=1)

5. 50.680 16,416.290 ↓ 17.6 3,570 1

Nested Loop Anti Join (cost=47.60..15,731.69 rows=203 width=11) (actual time=280.527..16,416.290 rows=3,570 loops=1)

6. 25.361 16,100.072 ↓ 100.6 20,426 1

Nested Loop (cost=47.04..13,971.21 rows=203 width=11) (actual time=67.966..16,100.072 rows=20,426 loops=1)

7. 66.980 127.799 ↓ 13.3 3,688 1

Bitmap Heap Scan on mbw_animal a (cost=46.48..7,118.56 rows=278 width=11) (actual time=60.960..127.799 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=661
8. 60.819 60.819 ↓ 4.2 7,908 1

Bitmap Index Scan on mbw_animal_62d4a177 (cost=0.00..46.41 rows=1,864 width=0) (actual time=60.819..60.819 rows=7,908 loops=1)

  • Index Cond: (propriedade_id = 3144)
9. 15,946.912 15,946.912 ↓ 1.5 6 3,688

Index Scan using vw_modelo_reprodutivo_data_hora_cobertura_idx on vw_modelo_reprodutivo cb (cost=0.56..24.61 rows=4 width=4) (actual time=0.734..4.324 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
10. 265.538 265.538 ↑ 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.013..0.013 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[])))
11. 3.223 2,411.875 ↓ 757.0 757 1

Nested Loop Anti Join (cost=0.99..58.03 rows=1 width=11) (actual time=2,068.830..2,411.875 rows=757 loops=1)

12. 5.295 2,113.203 ↓ 3,837.0 3,837 1

Nested Loop (cost=0.43..42.55 rows=1 width=11) (actual time=2,068.811..2,113.203 rows=3,837 loops=1)

13. 2,077.056 2,077.056 ↓ 1,028.4 5,142 1

CTE Scan on animais_aptos apt (cost=0.00..0.10 rows=5 width=4) (actual time=2,068.751..2,077.056 rows=5,142 loops=1)

14. 30.852 30.852 ↑ 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.006..0.006 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
15. 295.449 295.449 ↑ 1.0 1 3,837

Index Scan using vw_modelo_reprodutivo_data_hora_cobertura_idx on vw_modelo_reprodutivo (cost=0.56..8.02 rows=1 width=4) (actual time=0.077..0.077 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))
  • Filter: (contrato_id = 16)
16. 0.283 138.279 ↓ 46.0 46 1

Nested Loop Anti Join (cost=0.99..59.27 rows=1 width=11) (actual time=134.612..138.279 rows=46 loops=1)

17. 1.570 18.229 ↓ 229.0 229 1

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

18. 1.233 1.233 ↓ 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.233 rows=5,142 loops=1)

19. 15.426 15.426 ↓ 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.003..0.003 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
20. 119.767 119.767 ↑ 1.0 1 229

Index Scan using vw_modelo_reprodutivo_data_hora_cobertura_idx on vw_modelo_reprodutivo vw_modelo_reprodutivo_1 (cost=0.56..8.63 rows=1 width=4) (actual time=0.523..0.523 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))
  • Filter: (contrato_id = 16)
Planning time : 9.119 ms
Execution time : 18,982.547 ms