explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JjT4

Settings
# exclusive inclusive rows x rows loops node
1. 80,682.977 80,682.977 ↑ 1.0 1 1

CTE Scan on contagem c (cost=217,802.18..217,802.20 rows=1 width=1,224) (actual time=80,682.976..80,682.977 rows=1 loops=1)

2.          

CTE base

3. 5,608.705 8,832.377 ↑ 4.6 61,535 1

GroupAggregate (cost=0.56..208,479.72 rows=280,092 width=16) (actual time=82.871..8,832.377 rows=61,535 loops=1)

  • Group Key: v.femea_id
4. 3,223.672 3,223.672 ↑ 1.0 293,627 1

Index Only Scan using vw_modelo_reprodutivo_data_hora_cobertura_idx_order2 on vw_modelo_reprodutivo v (cost=0.56..134,133.54 rows=304,451 width=12) (actual time=82.700..3,223.672 rows=293,627 loops=1)

  • Index Cond: ((data_hora_cobertura <= '2020-06-24'::date) AND (contrato_id = 16))
  • Heap Fetches: 0
5.          

CTE agrupamento

6. 58.183 80,577.761 ↓ 5,458.0 5,458 1

Nested Loop (cost=2,659.35..9,320.87 rows=1 width=121) (actual time=42,655.150..80,577.761 rows=5,458 loops=1)

  • Join Filter: (b.cobertura_mais_recente = v_1.data_hora_cobertura)
  • Rows Removed by Join Filter: 21,017
7. 71.320 51,341.110 ↓ 454.8 5,458 1

Hash Join (cost=2,658.91..9,311.22 rows=12 width=128) (actual time=42,650.849..51,341.110 rows=5,458 loops=1)

  • Hash Cond: (b.femea_id = a.id)
8. 8,895.623 8,895.623 ↑ 4.6 61,535 1

CTE Scan on base b (cost=0.00..5,601.84 rows=280,092 width=12) (actual time=82.874..8,895.623 rows=61,535 loops=1)

9. 3.738 42,374.167 ↓ 15.3 6,851 1

Hash (cost=2,653.30..2,653.30 rows=449 width=116) (actual time=42,374.167..42,374.167 rows=6,851 loops=1)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 653kB
10. 7.892 42,370.429 ↓ 15.3 6,851 1

Nested Loop (cost=0.69..2,653.30 rows=449 width=116) (actual time=42,330.975..42,370.429 rows=6,851 loops=1)

11. 42,332.213 42,332.213 ↓ 7.6 7,581 1

Function Scan on obtenha_animais_estoque_por_propriedade_com_peso_projetado e (cost=0.25..10.25 rows=1,000 width=108) (actual time=42,330.951..42,332.213 rows=7,581 loops=1)

12. 30.324 30.324 ↑ 1.0 1 7,581

Index Scan using mbw_animal_pkey on mbw_animal a (cost=0.43..2.64 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=7,581)

  • Index Cond: (id = e.animal_id)
  • Filter: (tipo_id = ANY ('{1,2,3}'::integer[]))
  • Rows Removed by Filter: 0
13. 29,178.468 29,178.468 ↑ 1.2 5 5,458

Index Scan using vw_modelo_reprodutivo_femea_id_idx on vw_modelo_reprodutivo v_1 (cost=0.43..0.73 rows=6 width=21) (actual time=1.075..5.346 rows=5 loops=5,458)

  • Index Cond: (femea_id = a.id)
14.          

CTE contagem

15. 80,656.731 80,682.942 ↑ 1.0 1 1

Aggregate (cost=1.59..1.60 rows=1 width=1,224) (actual time=80,682.941..80,682.942 rows=1 loops=1)

16.          

Initplan (for Aggregate)

17. 1.370 1.370 ↓ 0.0 0 1

CTE Scan on resultado r (cost=0.06..0.08 rows=1 width=32) (actual time=1.370..1.370 rows=0 loops=1)

18.          

CTE resultado

19. 0.002 1.369 ↓ 0.0 0 1

GroupAggregate (cost=0.04..0.06 rows=1 width=40) (actual time=1.369..1.369 rows=0 loops=1)

  • Group Key: a_1.categoria_femea
20. 0.006 1.367 ↓ 0.0 0 1

Sort (cost=0.04..0.04 rows=1 width=32) (actual time=1.367..1.367 rows=0 loops=1)

  • Sort Key: a_1.categoria_femea
  • Sort Method: quicksort Memory: 25kB
21. 1.361 1.361 ↓ 0.0 0 1

CTE Scan on agrupamento a_1 (cost=0.00..0.03 rows=1 width=32) (actual time=1.361..1.361 rows=0 loops=1)

  • Filter: ((peso >= '0'::double precision) AND (peso <= '150'::double precision))
  • Rows Removed by Filter: 5,458
22. 1.275 1.275 ↓ 0.0 0 1

CTE Scan on resultado r_1 (cost=0.06..0.08 rows=1 width=32) (actual time=1.275..1.275 rows=0 loops=1)

23.          

CTE resultado

24. 0.002 1.274 ↓ 0.0 0 1

GroupAggregate (cost=0.04..0.06 rows=1 width=40) (actual time=1.274..1.274 rows=0 loops=1)

  • Group Key: a_2.categoria_femea
25. 0.006 1.272 ↓ 0.0 0 1

Sort (cost=0.04..0.04 rows=1 width=32) (actual time=1.272..1.272 rows=0 loops=1)

  • Sort Key: a_2.categoria_femea
  • Sort Method: quicksort Memory: 25kB
26. 1.266 1.266 ↓ 0.0 0 1

CTE Scan on agrupamento a_2 (cost=0.00..0.03 rows=1 width=32) (actual time=1.266..1.266 rows=0 loops=1)

  • Filter: ((peso > '150'::double precision) AND (peso <= '180'::double precision))
  • Rows Removed by Filter: 5,458
27. 1.446 1.446 ↓ 0.0 0 1

CTE Scan on resultado r_2 (cost=0.06..0.08 rows=1 width=32) (actual time=1.446..1.446 rows=0 loops=1)

28.          

CTE resultado

29. 0.002 1.444 ↓ 0.0 0 1

GroupAggregate (cost=0.04..0.06 rows=1 width=40) (actual time=1.444..1.444 rows=0 loops=1)

  • Group Key: a_3.categoria_femea
30. 0.007 1.442 ↓ 0.0 0 1

Sort (cost=0.04..0.04 rows=1 width=32) (actual time=1.442..1.442 rows=0 loops=1)

  • Sort Key: a_3.categoria_femea
  • Sort Method: quicksort Memory: 25kB
31. 1.435 1.435 ↓ 0.0 0 1

CTE Scan on agrupamento a_3 (cost=0.00..0.03 rows=1 width=32) (actual time=1.435..1.435 rows=0 loops=1)

  • Filter: ((peso > '180'::double precision) AND (peso <= '210'::double precision))
  • Rows Removed by Filter: 5,458
32. 1.394 1.394 ↓ 0.0 0 1

CTE Scan on resultado r_3 (cost=0.06..0.08 rows=1 width=32) (actual time=1.394..1.394 rows=0 loops=1)

33.          

CTE resultado

34. 0.001 1.392 ↓ 0.0 0 1

GroupAggregate (cost=0.04..0.06 rows=1 width=40) (actual time=1.392..1.392 rows=0 loops=1)

  • Group Key: a_4.categoria_femea
35. 0.007 1.391 ↓ 0.0 0 1

Sort (cost=0.04..0.04 rows=1 width=32) (actual time=1.391..1.391 rows=0 loops=1)

  • Sort Key: a_4.categoria_femea
  • Sort Method: quicksort Memory: 25kB
36. 1.384 1.384 ↓ 0.0 0 1

CTE Scan on agrupamento a_4 (cost=0.00..0.03 rows=1 width=32) (actual time=1.383..1.384 rows=0 loops=1)

  • Filter: ((peso > '210'::double precision) AND (peso <= '240'::double precision))
  • Rows Removed by Filter: 5,458
37. 3.317 3.317 ↑ 1.0 1 1

CTE Scan on resultado r_4 (cost=0.06..0.08 rows=1 width=32) (actual time=3.315..3.317 rows=1 loops=1)

38.          

CTE resultado

39. 0.408 2.501 ↑ 1.0 1 1

GroupAggregate (cost=0.04..0.06 rows=1 width=40) (actual time=2.501..2.501 rows=1 loops=1)

  • Group Key: a_5.categoria_femea
40. 0.588 2.093 ↓ 1,400.0 1,400 1

Sort (cost=0.04..0.04 rows=1 width=32) (actual time=1.933..2.093 rows=1,400 loops=1)

  • Sort Key: a_5.categoria_femea
  • Sort Method: quicksort Memory: 114kB
41. 1.505 1.505 ↓ 1,400.0 1,400 1

CTE Scan on agrupamento a_5 (cost=0.00..0.03 rows=1 width=32) (actual time=0.763..1.505 rows=1,400 loops=1)

  • Filter: ((peso > '240'::double precision) AND (peso <= '270'::double precision))
  • Rows Removed by Filter: 4,058
42. 1.309 1.309 ↓ 0.0 0 1

CTE Scan on resultado r_5 (cost=0.06..0.08 rows=1 width=32) (actual time=1.309..1.309 rows=0 loops=1)

43.          

CTE resultado

44. 0.002 1.308 ↓ 0.0 0 1

GroupAggregate (cost=0.04..0.06 rows=1 width=40) (actual time=1.308..1.308 rows=0 loops=1)

  • Group Key: a_6.categoria_femea
45. 0.007 1.306 ↓ 0.0 0 1

Sort (cost=0.04..0.04 rows=1 width=32) (actual time=1.306..1.306 rows=0 loops=1)

  • Sort Key: a_6.categoria_femea
  • Sort Method: quicksort Memory: 25kB
46. 1.299 1.299 ↓ 0.0 0 1

CTE Scan on agrupamento a_6 (cost=0.00..0.03 rows=1 width=32) (actual time=1.299..1.299 rows=0 loops=1)

  • Filter: ((peso > '270'::double precision) AND (peso <= '300'::double precision))
  • Rows Removed by Filter: 5,458
47. 1.254 1.254 ↓ 0.0 0 1

CTE Scan on resultado r_6 (cost=0.06..0.08 rows=1 width=32) (actual time=1.254..1.254 rows=0 loops=1)

48.          

CTE resultado

49. 0.002 1.253 ↓ 0.0 0 1

GroupAggregate (cost=0.04..0.06 rows=1 width=40) (actual time=1.253..1.253 rows=0 loops=1)

  • Group Key: a_7.categoria_femea
50. 0.006 1.251 ↓ 0.0 0 1

Sort (cost=0.04..0.04 rows=1 width=32) (actual time=1.251..1.251 rows=0 loops=1)

  • Sort Key: a_7.categoria_femea
  • Sort Method: quicksort Memory: 25kB
51. 1.245 1.245 ↓ 0.0 0 1

CTE Scan on agrupamento a_7 (cost=0.00..0.03 rows=1 width=32) (actual time=1.245..1.245 rows=0 loops=1)

  • Filter: ((peso > '300'::double precision) AND (peso <= '330'::double precision))
  • Rows Removed by Filter: 5,458
52. 1.707 1.707 ↓ 2.0 2 1

CTE Scan on resultado r_7 (cost=0.06..0.08 rows=1 width=32) (actual time=1.666..1.707 rows=2 loops=1)

53.          

CTE resultado

54. 0.083 1.676 ↓ 2.0 2 1

GroupAggregate (cost=0.04..0.06 rows=1 width=40) (actual time=1.639..1.676 rows=2 loops=1)

  • Group Key: a_8.categoria_femea
55. 0.145 1.593 ↓ 305.0 305 1

Sort (cost=0.04..0.04 rows=1 width=32) (actual time=1.558..1.593 rows=305 loops=1)

  • Sort Key: a_8.categoria_femea
  • Sort Method: quicksort Memory: 39kB
56. 1.448 1.448 ↓ 305.0 305 1

CTE Scan on agrupamento a_8 (cost=0.00..0.03 rows=1 width=32) (actual time=0.764..1.448 rows=305 loops=1)

  • Filter: ((peso > '330'::double precision) AND (peso <= '360'::double precision))
  • Rows Removed by Filter: 5,153
57. 1.231 1.231 ↓ 0.0 0 1

CTE Scan on resultado r_8 (cost=0.06..0.08 rows=1 width=32) (actual time=1.231..1.231 rows=0 loops=1)

58.          

CTE resultado

59. 0.001 1.230 ↓ 0.0 0 1

GroupAggregate (cost=0.04..0.06 rows=1 width=40) (actual time=1.230..1.230 rows=0 loops=1)

  • Group Key: a_9.categoria_femea
60. 0.007 1.229 ↓ 0.0 0 1

Sort (cost=0.04..0.04 rows=1 width=32) (actual time=1.229..1.229 rows=0 loops=1)

  • Sort Key: a_9.categoria_femea
  • Sort Method: quicksort Memory: 25kB
61. 1.222 1.222 ↓ 0.0 0 1

CTE Scan on agrupamento a_9 (cost=0.00..0.03 rows=1 width=32) (actual time=1.222..1.222 rows=0 loops=1)

  • Filter: ((peso > '360'::double precision) AND (peso <= '390'::double precision))
  • Rows Removed by Filter: 5,458
62. 1.326 1.326 ↓ 0.0 0 1

CTE Scan on resultado r_9 (cost=0.06..0.08 rows=1 width=32) (actual time=1.326..1.326 rows=0 loops=1)

63.          

CTE resultado

64. 0.001 1.325 ↓ 0.0 0 1

GroupAggregate (cost=0.04..0.06 rows=1 width=40) (actual time=1.325..1.325 rows=0 loops=1)

  • Group Key: a_10.categoria_femea
65. 0.006 1.324 ↓ 0.0 0 1

Sort (cost=0.04..0.04 rows=1 width=32) (actual time=1.324..1.324 rows=0 loops=1)

  • Sort Key: a_10.categoria_femea
  • Sort Method: quicksort Memory: 25kB
66. 1.318 1.318 ↓ 0.0 0 1

CTE Scan on agrupamento a_10 (cost=0.00..0.03 rows=1 width=32) (actual time=1.318..1.318 rows=0 loops=1)

  • Filter: ((peso > '390'::double precision) AND (peso <= '420'::double precision))
  • Rows Removed by Filter: 5,458
67. 4.778 4.778 ↓ 4.0 4 1

CTE Scan on resultado r_10 (cost=0.06..0.08 rows=1 width=32) (actual time=4.450..4.778 rows=4 loops=1)

68.          

CTE resultado

69. 0.935 4.751 ↓ 4.0 4 1

GroupAggregate (cost=0.04..0.06 rows=1 width=40) (actual time=4.434..4.751 rows=4 loops=1)

  • Group Key: a_11.categoria_femea
70. 1.791 3.816 ↓ 3,753.0 3,753 1

Sort (cost=0.04..0.04 rows=1 width=32) (actual time=3.406..3.816 rows=3,753 loops=1)

  • Sort Key: a_11.categoria_femea
  • Sort Method: quicksort Memory: 272kB
71. 2.025 2.025 ↓ 3,753.0 3,753 1

CTE Scan on agrupamento a_11 (cost=0.00..0.03 rows=1 width=32) (actual time=0.003..2.025 rows=3,753 loops=1)

  • Filter: ((peso > '420'::double precision) AND (peso <= '450'::double precision))
  • Rows Removed by Filter: 1,705
72. 1.199 1.199 ↓ 0.0 0 1

CTE Scan on resultado r_11 (cost=0.06..0.08 rows=1 width=32) (actual time=1.199..1.199 rows=0 loops=1)

73.          

CTE resultado

74. 0.001 1.198 ↓ 0.0 0 1

GroupAggregate (cost=0.04..0.06 rows=1 width=40) (actual time=1.198..1.198 rows=0 loops=1)

  • Group Key: a_12.categoria_femea
75. 0.008 1.197 ↓ 0.0 0 1

Sort (cost=0.04..0.04 rows=1 width=32) (actual time=1.197..1.197 rows=0 loops=1)

  • Sort Key: a_12.categoria_femea
  • Sort Method: quicksort Memory: 25kB
76. 1.189 1.189 ↓ 0.0 0 1

CTE Scan on agrupamento a_12 (cost=0.00..0.03 rows=1 width=32) (actual time=1.189..1.189 rows=0 loops=1)

  • Filter: ((peso > '450'::double precision) AND (peso <= '480'::double precision))
  • Rows Removed by Filter: 5,458
77. 1.182 1.182 ↓ 0.0 0 1

CTE Scan on resultado r_12 (cost=0.06..0.08 rows=1 width=32) (actual time=1.182..1.182 rows=0 loops=1)

78.          

CTE resultado

79. 0.001 1.181 ↓ 0.0 0 1

GroupAggregate (cost=0.04..0.06 rows=1 width=40) (actual time=1.181..1.181 rows=0 loops=1)

  • Group Key: a_13.categoria_femea
80. 0.005 1.180 ↓ 0.0 0 1

Sort (cost=0.04..0.04 rows=1 width=32) (actual time=1.180..1.180 rows=0 loops=1)

  • Sort Key: a_13.categoria_femea
  • Sort Method: quicksort Memory: 25kB
81. 1.175 1.175 ↓ 0.0 0 1

CTE Scan on agrupamento a_13 (cost=0.00..0.03 rows=1 width=32) (actual time=1.175..1.175 rows=0 loops=1)

  • Filter: ((peso > '480'::double precision) AND (peso <= '510'::double precision))
  • Rows Removed by Filter: 5,458
82. 1.219 1.219 ↓ 0.0 0 1

CTE Scan on resultado r_13 (cost=0.06..0.08 rows=1 width=32) (actual time=1.219..1.219 rows=0 loops=1)

83.          

CTE resultado

84. 0.001 1.218 ↓ 0.0 0 1

GroupAggregate (cost=0.04..0.06 rows=1 width=40) (actual time=1.218..1.218 rows=0 loops=1)

  • Group Key: a_14.categoria_femea
85. 0.007 1.217 ↓ 0.0 0 1

Sort (cost=0.04..0.04 rows=1 width=32) (actual time=1.217..1.217 rows=0 loops=1)

  • Sort Key: a_14.categoria_femea
  • Sort Method: quicksort Memory: 25kB
86. 1.210 1.210 ↓ 0.0 0 1

CTE Scan on agrupamento a_14 (cost=0.00..0.03 rows=1 width=32) (actual time=1.210..1.210 rows=0 loops=1)

  • Filter: ((peso > '510'::double precision) AND (peso <= '540'::double precision))
  • Rows Removed by Filter: 5,458
87. 1.185 1.185 ↓ 0.0 0 1

CTE Scan on resultado r_14 (cost=0.06..0.08 rows=1 width=32) (actual time=1.185..1.185 rows=0 loops=1)

88.          

CTE resultado

89. 0.002 1.184 ↓ 0.0 0 1

GroupAggregate (cost=0.04..0.06 rows=1 width=40) (actual time=1.183..1.184 rows=0 loops=1)

  • Group Key: a_15.categoria_femea
90. 0.006 1.182 ↓ 0.0 0 1

Sort (cost=0.04..0.04 rows=1 width=32) (actual time=1.182..1.182 rows=0 loops=1)

  • Sort Key: a_15.categoria_femea
  • Sort Method: quicksort Memory: 25kB
91. 1.176 1.176 ↓ 0.0 0 1

CTE Scan on agrupamento a_15 (cost=0.00..0.03 rows=1 width=32) (actual time=1.176..1.176 rows=0 loops=1)

  • Filter: ((peso > '540'::double precision) AND (peso <= '570'::double precision))
  • Rows Removed by Filter: 5,458
92. 1.019 1.019 ↓ 0.0 0 1

CTE Scan on resultado r_15 (cost=0.06..0.08 rows=1 width=32) (actual time=1.019..1.019 rows=0 loops=1)

93.          

CTE resultado

94. 0.002 1.018 ↓ 0.0 0 1

GroupAggregate (cost=0.04..0.06 rows=1 width=40) (actual time=1.018..1.018 rows=0 loops=1)

  • Group Key: a_16.categoria_femea
95. 0.005 1.016 ↓ 0.0 0 1

Sort (cost=0.04..0.04 rows=1 width=32) (actual time=1.016..1.016 rows=0 loops=1)

  • Sort Key: a_16.categoria_femea
  • Sort Method: quicksort Memory: 25kB
96. 1.011 1.011 ↓ 0.0 0 1

CTE Scan on agrupamento a_16 (cost=0.00..0.03 rows=1 width=32) (actual time=1.011..1.011 rows=0 loops=1)