explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6c7B

Settings
# exclusive inclusive rows x rows loops node
1. 13,365.954 29,845.693 ↑ 1,000.0 1 1

GroupAggregate (cost=48,815.89..48,900.89 rows=1,000 width=60) (actual time=29,845.693..29,845.693 rows=1 loops=1)

  • Group Key: r.grupo, n.grupo, d.grupo, c.grupo, e.grupo, ai.grupo, vd.grupo, esd.grupo
2.          

CTE animais_inicio

3. 0.254 77.504 ↑ 13.3 75 1

Nested Loop (cost=0.68..8,085.25 rows=1,000 width=8) (actual time=76.453..77.504 rows=75 loops=1)

4. 76.500 76.500 ↑ 13.3 75 1

Function Scan on obtenha_animais_estoque_por_contrato e_1 (cost=0.25..10.25 rows=1,000 width=4) (actual time=76.447..76.500 rows=75 loops=1)

5. 0.750 0.750 ↑ 1.0 1 75

Index Scan using mbw_animal_pkey on mbw_animal a (cost=0.43..8.06 rows=1 width=8) (actual time=0.009..0.010 rows=1 loops=75)

  • Index Cond: (id = e_1.animal_id)
6.          

CTE entrada_animais_nascidos

7. 0.065 0.065 ↑ 1.9 40 1

Index Scan using mbw_animal_868819a8 on mbw_animal a_1 (cost=0.43..7,510.94 rows=74 width=12) (actual time=0.009..0.065 rows=40 loops=1)

  • Index Cond: (contrato_id = 120)
  • Filter: ((tipo_id <> ALL ('{7,9}'::integer[])) AND (data_aquisicao_inicial >= '2017-01-01'::date) AND (data_aquisicao_inicial <= '2019-07-05'::date) AND (tipo_entrada_inicial = 0))
  • Rows Removed by Filter: 121
8.          

CTE entrada_animais_compra

9. 0.064 0.064 ↑ 2.8 37 1

Index Scan using mbw_animal_868819a8 on mbw_animal a_2 (cost=0.43..7,510.94 rows=104 width=8) (actual time=0.005..0.064 rows=37 loops=1)

  • Index Cond: (contrato_id = 120)
  • Filter: ((tipo_id <> ALL ('{7,9}'::integer[])) AND (data_aquisicao_inicial >= '2017-01-01'::date) AND (data_aquisicao_inicial <= '2019-07-05'::date) AND (tipo_entrada_inicial = 1))
  • Rows Removed by Filter: 124
10.          

CTE entrada_animais_doacao

11. 0.057 0.057 ↑ 1.0 1 1

Index Scan using mbw_animal_868819a8 on mbw_animal a_3 (cost=0.43..7,510.94 rows=1 width=8) (actual time=0.036..0.057 rows=1 loops=1)

  • Index Cond: (contrato_id = 120)
  • Filter: ((tipo_id <> ALL ('{7,9}'::integer[])) AND (data_aquisicao_inicial >= '2017-01-01'::date) AND (data_aquisicao_inicial <= '2019-07-05'::date) AND (tipo_entrada_inicial = 2))
  • Rows Removed by Filter: 160
12.          

CTE entrada_animais_emprestimo

13. 0.059 0.059 ↓ 2.0 2 1

Index Scan using mbw_animal_868819a8 on mbw_animal a_4 (cost=0.43..7,510.94 rows=1 width=8) (actual time=0.034..0.059 rows=2 loops=1)

  • Index Cond: (contrato_id = 120)
  • Filter: ((tipo_id <> ALL ('{7,9}'::integer[])) AND (data_aquisicao_inicial >= '2017-01-01'::date) AND (data_aquisicao_inicial <= '2019-07-05'::date) AND (tipo_entrada_inicial = 3))
  • Rows Removed by Filter: 159
14.          

CTE entrada_animais_ajuste_inventario

15. 0.056 0.056 ↓ 2.0 2 1

Index Scan using mbw_animal_868819a8 on mbw_animal a_5 (cost=0.43..7,510.94 rows=1 width=8) (actual time=0.033..0.056 rows=2 loops=1)

  • Index Cond: (contrato_id = 120)
  • Filter: ((tipo_id <> ALL ('{7,9}'::integer[])) AND (data_aquisicao_inicial >= '2017-01-01'::date) AND (data_aquisicao_inicial <= '2019-07-05'::date) AND (tipo_entrada_inicial = 5))
  • Rows Removed by Filter: 159
16.          

CTE entrada_venda_devolucoes

17. 0.009 0.029 ↓ 9.0 9 1

Nested Loop (cost=20.33..2,916.12 rows=1 width=8) (actual time=0.015..0.029 rows=9 loops=1)

18. 0.005 0.011 ↓ 9.0 9 1

Bitmap Heap Scan on mbw_vendaitens vi (cost=19.90..2,907.67 rows=1 width=12) (actual time=0.010..0.011 rows=9 loops=1)

  • Recheck Cond: (contrato_id = 120)
  • Filter: ((data_devolucao >= '2017-01-01'::date) AND (data_devolucao <= '2019-07-05'::date))
  • Rows Removed by Filter: 24
  • Heap Blocks: exact=2
19. 0.006 0.006 ↑ 30.2 33 1

Bitmap Index Scan on mbw_vendaitens_868819a8 (cost=0.00..19.90 rows=997 width=0) (actual time=0.006..0.006 rows=33 loops=1)

  • Index Cond: (contrato_id = 120)
20. 0.009 0.009 ↑ 1.0 1 9

Index Only Scan using mbw_animal_pkey on mbw_animal a_6 (cost=0.43..8.45 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=9)

  • Index Cond: (id = vi.animal_id)
  • Heap Fetches: 9
21.          

CTE entrada_saida_devolucao

22. 0.004 0.017 ↓ 7.0 7 1

Nested Loop (cost=0.72..148.64 rows=1 width=8) (actual time=0.007..0.017 rows=7 loops=1)

23. 0.006 0.006 ↓ 7.0 7 1

Index Scan using mbw_saida_868819a8 on mbw_saida s (cost=0.29..140.18 rows=1 width=12) (actual time=0.004..0.006 rows=7 loops=1)

  • Index Cond: (contrato_id = 120)
  • Filter: ((data_devolucao >= '2017-01-01'::date) AND (data_devolucao <= '2019-07-05'::date) AND (tipo = ANY ('{2,3,5}'::integer[])))
  • Rows Removed by Filter: 10
24. 0.007 0.007 ↑ 1.0 1 7

Index Only Scan using mbw_animal_pkey on mbw_animal a_7 (cost=0.43..8.45 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=7)

  • Index Cond: (id = s.animal_id)
  • Heap Fetches: 7
25. 12,750.646 16,479.739 ↓ 27,972.0 27,972,000 1

Sort (cost=111.18..113.68 rows=1,000 width=60) (actual time=13,912.844..16,479.739 rows=27,972,000 loops=1)

  • Sort Key: r.grupo, n.grupo, d.grupo, c.grupo, e.grupo, ai.grupo, vd.grupo, esd.grupo
  • Sort Method: external sort Disk: 1914024kB
26. 3,051.556 3,729.093 ↓ 27,972.0 27,972,000 1

Hash Left Join (cost=5.95..61.35 rows=1,000 width=60) (actual time=76.855..3,729.093 rows=27,972,000 loops=1)

  • Hash Cond: (r.contrato_id = esd.contrato_id)
27. 455.863 677.516 ↓ 3,996.0 3,996,000 1

Hash Left Join (cost=5.91..57.51 rows=1,000 width=60) (actual time=76.831..677.516 rows=3,996,000 loops=1)

  • Hash Cond: (r.contrato_id = vd.contrato_id)
28. 69.917 221.621 ↓ 444.0 444,000 1

Hash Left Join (cost=5.88..53.68 rows=1,000 width=52) (actual time=76.797..221.621 rows=444,000 loops=1)

  • Hash Cond: (r.contrato_id = ai.contrato_id)
29. 34.647 151.645 ↓ 222.0 222,000 1

Hash Left Join (cost=5.85..49.85 rows=1,000 width=44) (actual time=76.735..151.645 rows=222,000 loops=1)

  • Hash Cond: (r.contrato_id = e.contrato_id)
30. 22.203 116.938 ↓ 111.0 111,000 1

Hash Left Join (cost=5.82..46.02 rows=1,000 width=36) (actual time=76.673..116.938 rows=111,000 loops=1)

  • Hash Cond: (r.contrato_id = d.contrato_id)
31. 16.276 94.677 ↓ 111.0 111,000 1

Hash Left Join (cost=5.79..42.19 rows=1,000 width=28) (actual time=76.612..94.677 rows=111,000 loops=1)

  • Hash Cond: (r.contrato_id = c.contrato_id)
32. 0.646 78.329 ↓ 3.0 3,000 1

Hash Left Join (cost=2.41..29.86 rows=1,000 width=20) (actual time=76.537..78.329 rows=3,000 loops=1)

  • Hash Cond: (r.contrato_id = n.contrato_id)
33. 77.603 77.603 ↑ 13.3 75 1

CTE Scan on animais_inicio r (cost=0.00..20.00 rows=1,000 width=12) (actual time=76.454..77.603 rows=75 loops=1)

34. 0.004 0.080 ↑ 1.9 40 1

Hash (cost=1.48..1.48 rows=74 width=12) (actual time=0.080..0.080 rows=40 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
35. 0.076 0.076 ↑ 1.9 40 1

CTE Scan on entrada_animais_nascidos n (cost=0.00..1.48 rows=74 width=12) (actual time=0.009..0.076 rows=40 loops=1)

36. 0.002 0.072 ↑ 2.8 37 1

Hash (cost=2.08..2.08 rows=104 width=12) (actual time=0.072..0.072 rows=37 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
37. 0.070 0.070 ↑ 2.8 37 1

CTE Scan on entrada_animais_compra c (cost=0.00..2.08 rows=104 width=12) (actual time=0.005..0.070 rows=37 loops=1)

38. 0.001 0.058 ↑ 1.0 1 1

Hash (cost=0.02..0.02 rows=1 width=12) (actual time=0.058..0.058 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
39. 0.057 0.057 ↑ 1.0 1 1

CTE Scan on entrada_animais_doacao d (cost=0.00..0.02 rows=1 width=12) (actual time=0.036..0.057 rows=1 loops=1)

40. 0.001 0.060 ↓ 2.0 2 1

Hash (cost=0.02..0.02 rows=1 width=12) (actual time=0.060..0.060 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
41. 0.059 0.059 ↓ 2.0 2 1

CTE Scan on entrada_animais_emprestimo e (cost=0.00..0.02 rows=1 width=12) (actual time=0.034..0.059 rows=2 loops=1)

42. 0.000 0.059 ↓ 2.0 2 1

Hash (cost=0.02..0.02 rows=1 width=12) (actual time=0.059..0.059 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
43. 0.059 0.059 ↓ 2.0 2 1

CTE Scan on entrada_animais_ajuste_inventario ai (cost=0.00..0.02 rows=1 width=12) (actual time=0.035..0.059 rows=2 loops=1)

44. 0.001 0.032 ↓ 9.0 9 1

Hash (cost=0.02..0.02 rows=1 width=12) (actual time=0.032..0.032 rows=9 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
45. 0.031 0.031 ↓ 9.0 9 1

CTE Scan on entrada_venda_devolucoes vd (cost=0.00..0.02 rows=1 width=12) (actual time=0.015..0.031 rows=9 loops=1)

46. 0.003 0.021 ↓ 7.0 7 1

Hash (cost=0.02..0.02 rows=1 width=8) (actual time=0.021..0.021 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
47. 0.018 0.018 ↓ 7.0 7 1

CTE Scan on entrada_saida_devolucao esd (cost=0.00..0.02 rows=1 width=8) (actual time=0.007..0.018 rows=7 loops=1)

Planning time : 2.574 ms
Execution time : 50,574.458 ms