explain.depesz.com

PostgreSQL's explain analyze made readable

Result: R1pB

Settings
# exclusive inclusive rows x rows loops node
1. 115,545.666 115,545.666 ↑ 1.0 1 1

CTE Scan on contagem c (cost=342,654.40..342,654.42 rows=1 width=1,224) (actual time=115,545.666..115,545.666 rows=1 loops=1)

2.          

CTE base

3. 2,532.398 63,186.667 ↑ 4.4 61,535 1

GroupAggregate (cost=0.56..333,563.31 rows=270,350 width=16) (actual time=7,946.437..63,186.667 rows=61,535 loops=1)

  • Group Key: v.femea_id
4. 60,654.269 60,654.269 ↓ 1.0 293,627 1

Index Only Scan using vw_modelo_reprodutivo_data_hora_cobertura_idx_order on vw_modelo_reprodutivo v (cost=0.56..261,811.11 rows=292,240 width=12) (actual time=7,934.160..60,654.269 rows=293,627 loops=1)

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

CTE agrupamento

6. 53.083 115,461.556 ↓ 5,458.0 5,458 1

Nested Loop (cost=2,659.35..9,089.50 rows=1 width=121) (actual time=41,268.974..115,461.556 rows=5,458 loops=1)

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

Hash Join (cost=2,658.91..9,079.85 rows=12 width=128) (actual time=41,227.007..95,694.177 rows=5,458 loops=1)

  • Hash Cond: (b.femea_id = a.id)
8. 63,243.354 63,243.354 ↑ 4.4 61,535 1

CTE Scan on base b (cost=0.00..5,407.00 rows=270,350 width=12) (actual time=7,946.440..63,243.354 rows=61,535 loops=1)

9. 2.296 32,385.760 ↓ 15.3 6,851 1

Hash (cost=2,653.30..2,653.30 rows=449 width=116) (actual time=32,385.759..32,385.760 rows=6,851 loops=1)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 653kB
10. 3.033 32,383.464 ↓ 15.3 6,851 1

Nested Loop (cost=0.69..2,653.30 rows=449 width=116) (actual time=32,356.917..32,383.464 rows=6,851 loops=1)

11. 32,357.688 32,357.688 ↓ 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=32,356.895..32,357.688 rows=7,581 loops=1)

12. 22.743 22.743 ↑ 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.003..0.003 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. 19,714.296 19,714.296 ↑ 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=0.820..3.612 rows=5 loops=5,458)

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

CTE contagem

15. 115,528.810 115,545.642 ↑ 1.0 1 1

Aggregate (cost=1.59..1.60 rows=1 width=1,224) (actual time=115,545.642..115,545.642 rows=1 loops=1)

16.          

Initplan (for Aggregate)

17. 1.451 1.451 ↓ 0.0 0 1

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

18.          

CTE resultado

19. 0.001 1.449 ↓ 0.0 0 1

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

  • Group Key: a_1.categoria_femea
20. 0.044 1.448 ↓ 0.0 0 1

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

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

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

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

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

23.          

CTE resultado

24. 0.002 1.201 ↓ 0.0 0 1

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

  • Group Key: a_2.categoria_femea
25. 0.005 1.199 ↓ 0.0 0 1

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

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

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

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

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

28.          

CTE resultado

29. 0.002 0.875 ↓ 0.0 0 1

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

  • Group Key: a_3.categoria_femea
30. 0.005 0.873 ↓ 0.0 0 1

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

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

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

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

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

33.          

CTE resultado

34. 0.001 0.757 ↓ 0.0 0 1

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

  • Group Key: a_4.categoria_femea
35. 0.005 0.756 ↓ 0.0 0 1

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

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

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

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

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

38.          

CTE resultado

39. 0.195 1.254 ↑ 1.0 1 1

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

  • Group Key: a_5.categoria_femea
40. 0.316 1.059 ↓ 1,400.0 1,400 1

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

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

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

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

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

43.          

CTE resultado

44. 0.001 0.588 ↓ 0.0 0 1

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

  • Group Key: a_6.categoria_femea
45. 0.005 0.587 ↓ 0.0 0 1

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

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

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

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

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

48.          

CTE resultado

49. 0.001 0.540 ↓ 0.0 0 1

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

  • Group Key: a_7.categoria_femea
50. 0.003 0.539 ↓ 0.0 0 1

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

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

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

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

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

53.          

CTE resultado

54. 0.041 0.765 ↓ 2.0 2 1

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

  • Group Key: a_8.categoria_femea
55. 0.072 0.724 ↓ 305.0 305 1

Sort (cost=0.04..0.04 rows=1 width=32) (actual time=0.707..0.724 rows=305 loops=1)

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

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

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

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

58.          

CTE resultado

59. 0.000 0.566 ↓ 0.0 0 1

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

  • Group Key: a_9.categoria_femea
60. 0.003 0.566 ↓ 0.0 0 1

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

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

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

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

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

63.          

CTE resultado

64. 0.000 0.600 ↓ 0.0 0 1

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

  • Group Key: a_10.categoria_femea
65. 0.003 0.600 ↓ 0.0 0 1

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

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

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

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

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

68.          

CTE resultado

69. 0.799 3.929 ↓ 4.0 4 1

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

  • Group Key: a_11.categoria_femea
70. 1.484 3.130 ↓ 3,753.0 3,753 1

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

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

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

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

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

73.          

CTE resultado

74. 0.000 0.576 ↓ 0.0 0 1

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

  • Group Key: a_12.categoria_femea
75. 0.006 0.576 ↓ 0.0 0 1

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

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

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

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

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

78.          

CTE resultado

79. 0.002 0.552 ↓ 0.0 0 1

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

  • Group Key: a_13.categoria_femea
80. 0.003 0.550 ↓ 0.0 0 1

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

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

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

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

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

83.          

CTE resultado

84. 0.001 0.833 ↓ 0.0 0 1

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

  • Group Key: a_14.categoria_femea
85. 0.004 0.832 ↓ 0.0 0 1

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

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

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

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

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

88.          

CTE resultado

89. 0.001 0.569 ↓ 0.0 0 1

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

  • Group Key: a_15.categoria_femea
90. 0.005 0.568 ↓ 0.0 0 1

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

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

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

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

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

93.          

CTE resultado

94. 0.001 0.519 ↓ 0.0 0 1

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

  • Group Key: a_16.categoria_femea
95. 0.003 0.518 ↓ 0.0 0 1

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

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

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