explain.depesz.com

PostgreSQL's explain analyze made readable

Result: eRBQ

Settings
# exclusive inclusive rows x rows loops node
1. 23.641 394,586.754 ↓ 1.4 4,771 1

HashAggregate (cost=1,846,142.44..1,846,364.48 rows=3,416 width=150) (actual time=394,584.746..394,586.754 rows=4,771 loops=1)

  • Group Key: (r.data_hora_cobertura + '295 days'::interval), CASE WHEN (r.data_parto IS NOT NULL) THEN 'Parida'::text ELSE ''::text END, r.data_parto, r.categoria, r.femea_identificacao_usual, r.femea_id, CASE WHEN (r.data_parto IS NULL) THEN (r.data_hora_cobertura + '315 days'::interval) ELSE ((r.data_parto + CASE WHEN (r.categoria = 'Novilha'::text) THEN $6 WHEN (r.categoria = 'Precose'::text) THEN $7 WHEN (r.categoria = 'Primípara'::text) THEN $8 WHEN (r.categoria = 'Secondípara'::text) THEN $9 WHEN (r.categoria = 'Multipara'::text) THEN $10 WHEN (r.categoria = 'Solteira'::text) THEN $11 ELSE NULL::integer END))::timestamp with time zone END, CASE WHEN (r.data_parto IS NULL) THEN (r.data_hora_cobertura + '315 days'::interval) ELSE ((r.data_parto + CASE WHEN (r.categoria = 'Novilha'::text) THEN $12 WHEN (r.categoria = 'Precose'::text) THEN $13 WHEN (r.categoria = 'Primípara'::text) THEN $14 WHEN (r.categoria = 'Secondípara'::text) THEN $15 WHEN (r.categoria = 'Multipara'::text) THEN $16 WHEN (r.categoria = 'Solteira'::text) THEN $17 ELSE NULL::integer END))::timestamp with time zone END, COALESCE(dias_medios_descanso.tempo_descanco, '-1'::integer), COALESCE((('now'::cstring)::date - aguardando_inseminacao.data_parto), '-1'::integer), dias_medios_descanso.data_realizacao_primeira_ia, dias_medios_descanso.data_realizacao_segunda_ia, dias_medios_descanso.data_realizacao_terceira_ia, COALESCE(((dg.data_exame - (dg.data_hora_cobertura)::date)), 0), COALESCE(((dg2.data_exame - (dg2.data_hora_cobertura)::date)), 0), COALESCE(((dg3.data_exame - (dg3.data_hora_cobertura)::date)), 0)
2.          

CTE resultado

3. 16.434 73,409.778 ↓ 4,765.0 4,765 1

HashAggregate (cost=583,856.72..583,856.73 rows=1 width=115) (actual time=73,407.921..73,409.778 rows=4,765 loops=1)

  • Group Key: CASE WHEN (c_1.categoria_dasborad = 'Solteiras'::text) THEN c_1.categoria_dasborad ELSE vw_4.categoria_femea END, p_2.data_parto, c_1.femea_id, c_1.data_hora_cobertura, c_1.identificacao_usual
4.          

CTE classificar_femeas

5. 14.907 68,030.048 ↓ 2,802.8 25,225 1

WindowAgg (cost=107.05..107.23 rows=9 width=59) (actual time=68,011.342..68,030.048 rows=25,225 loops=1)

6. 21.886 68,015.141 ↓ 2,802.8 25,225 1

Sort (cost=107.05..107.07 rows=9 width=59) (actual time=68,011.328..68,015.141 rows=25,225 loops=1)

  • Sort Key: vw_3.femea_id, vw_3.data_hora_cobertura DESC
  • Sort Method: quicksort Memory: 2739kB
7. 29.319 67,993.255 ↓ 2,802.8 25,225 1

Nested Loop Left Join (cost=1.12..106.90 rows=9 width=59) (actual time=67,324.242..67,993.255 rows=25,225 loops=1)

8. 7.147 67,888.261 ↓ 2,802.8 25,225 1

Nested Loop Left Join (cost=0.69..87.36 rows=9 width=52) (actual time=67,324.222..67,888.261 rows=25,225 loops=1)

9. 67,328.539 67,328.539 ↓ 533.9 4,805 1

Function Scan on reprodutivo_femeas_aptas (cost=0.26..14.01 rows=9 width=36) (actual time=67,322.530..67,328.539 rows=4,805 loops=1)

  • Filter: (((retirada_em IS FALSE) OR ((retirada_em IS TRUE) AND (inseminda_em_atual IS TRUE))) AND (categoria_dasborad = ANY ('{Paridas,Gestantes,Solteiras}'::text[])))
  • Rows Removed by Filter: 2799
10. 552.575 552.575 ↓ 5.0 5 4,805

Index Scan using mbw_cobertura_24f7cd40 on mbw_cobertura vw_3 (cost=0.43..8.14 rows=1 width=16) (actual time=0.024..0.115 rows=5 loops=4,805)

  • Index Cond: (reprodutivo_femeas_aptas.femea_id = femea_id)
  • Filter: (propriedade_id = 3232)
  • Rows Removed by Filter: 0
11. 75.675 75.675 ↑ 1.0 1 25,225

Index Scan using mbw_animal_pkey on mbw_animal a_1 (cost=0.43..2.16 rows=1 width=11) (actual time=0.003..0.003 rows=1 loops=25,225)

  • Index Cond: (id = vw_3.femea_id)
12. 18.865 73,393.344 ↓ 4,766.0 4,766 1

Nested Loop Left Join (cost=0.65..583,749.48 rows=1 width=115) (actual time=68,050.419..73,393.344 rows=4,766 loops=1)

13. 2,057.407 73,255.354 ↓ 4,765.0 4,765 1

Hash Join (cost=0.21..583,744.57 rows=1 width=111) (actual time=68,047.840..73,255.354 rows=4,765 loops=1)

  • Hash Cond: (vw_4.cobertura_id = c_1.cobertura_id)
14. 3,153.284 3,153.284 ↓ 1.0 6,631,281 1

Seq Scan on vw_modelo_reprodutivo vw_4 (cost=0.00..558,913.80 rows=6,621,480 width=13) (actual time=0.031..3,153.284 rows=6,631,281 loops=1)

15. 1.512 68,044.663 ↓ 4,767.0 4,767 1

Hash (cost=0.20..0.20 rows=1 width=106) (actual time=68,044.663..68,044.663 rows=4,767 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 381kB
16. 68,043.151 68,043.151 ↓ 4,767.0 4,767 1

CTE Scan on classificar_femeas c_1 (cost=0.00..0.20 rows=1 width=106) (actual time=68,011.353..68,043.151 rows=4,767 loops=1)

  • Filter: (ordem = 1)
  • Rows Removed by Filter: 20458
17. 119.125 119.125 ↑ 1.0 1 4,765

Index Scan using mbw_parto_24f7cd40 on mbw_parto p_2 (cost=0.43..4.89 rows=1 width=8) (actual time=0.024..0.025 rows=1 loops=4,765)

  • Index Cond: (c_1.femea_id = femea_id)
  • Filter: ((data_parto >= '2018-05-01'::date) AND (data_parto <= '2019-02-28'::date))
  • Rows Removed by Filter: 2
18.          

CTE femeas_aptas

19. 64,308.500 64,308.500 ↓ 533.9 4,805 1

Function Scan on reprodutivo_femeas_aptas reprodutivo_femeas_aptas_1 (cost=0.26..14.01 rows=9 width=4) (actual time=64,303.112..64,308.500 rows=4,805 loops=1)

  • Filter: (((retirada_em IS FALSE) OR ((retirada_em IS TRUE) AND (inseminda_em_atual IS TRUE))) AND (categoria_dasborad = ANY ('{Paridas,Gestantes,Solteiras}'::text[])))
  • Rows Removed by Filter: 2799
20.          

Initplan (for HashAggregate)

21. 0.020 0.020 ↓ 0.0 0 1

Index Scan using mbw_planejamentoestacaomonta_cbe4ebd5 on mbw_planejamentoestacaomonta (cost=0.28..2.07 rows=1 width=8) (actual time=0.020..0.020 rows=0 loops=1)

  • Index Cond: (estacao_monta_id = 44)
  • Filter: (categoria = 1)
22. 0.000 0.000 ↓ 0.0 0

Index Scan using mbw_planejamentoestacaomonta_cbe4ebd5 on mbw_planejamentoestacaomonta mbw_planejamentoestacaomonta_1 (cost=0.28..2.07 rows=1 width=8) (never executed)

  • Index Cond: (estacao_monta_id = 44)
  • Filter: (categoria = 2)
23. 1.736 1.736 ↓ 0.0 0 1

Index Scan using mbw_planejamentoestacaomonta_cbe4ebd5 on mbw_planejamentoestacaomonta mbw_planejamentoestacaomonta_2 (cost=0.28..2.07 rows=1 width=8) (actual time=1.736..1.736 rows=0 loops=1)

  • Index Cond: (estacao_monta_id = 44)
  • Filter: (categoria = 3)
24. 0.000 0.000 ↓ 0.0 0

Index Scan using mbw_planejamentoestacaomonta_cbe4ebd5 on mbw_planejamentoestacaomonta mbw_planejamentoestacaomonta_3 (cost=0.28..2.07 rows=1 width=8) (never executed)

  • Index Cond: (estacao_monta_id = 44)
  • Filter: (categoria = 4)
25. 0.000 0.000 ↓ 0.0 0

Index Scan using mbw_planejamentoestacaomonta_cbe4ebd5 on mbw_planejamentoestacaomonta mbw_planejamentoestacaomonta_4 (cost=0.28..2.07 rows=1 width=8) (never executed)

  • Index Cond: (estacao_monta_id = 44)
  • Filter: (categoria = 5)
26. 0.000 0.000 ↓ 0.0 0

Index Scan using mbw_planejamentoestacaomonta_cbe4ebd5 on mbw_planejamentoestacaomonta mbw_planejamentoestacaomonta_5 (cost=0.28..2.07 rows=1 width=8) (never executed)

  • Index Cond: (estacao_monta_id = 44)
  • Filter: (categoria = 6)
27. 0.001 0.001 ↓ 0.0 0 1

Index Scan using mbw_planejamentoestacaomonta_cbe4ebd5 on mbw_planejamentoestacaomonta mbw_planejamentoestacaomonta_6 (cost=0.28..2.07 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: (estacao_monta_id = 44)
  • Filter: (categoria = 1)
28. 0.000 0.000 ↓ 0.0 0

Index Scan using mbw_planejamentoestacaomonta_cbe4ebd5 on mbw_planejamentoestacaomonta mbw_planejamentoestacaomonta_7 (cost=0.28..2.07 rows=1 width=4) (never executed)

  • Index Cond: (estacao_monta_id = 44)
  • Filter: (categoria = 2)
29. 0.006 0.006 ↓ 0.0 0 1

Index Scan using mbw_planejamentoestacaomonta_cbe4ebd5 on mbw_planejamentoestacaomonta mbw_planejamentoestacaomonta_8 (cost=0.28..2.07 rows=1 width=4) (actual time=0.006..0.006 rows=0 loops=1)

  • Index Cond: (estacao_monta_id = 44)
  • Filter: (categoria = 3)
30. 0.000 0.000 ↓ 0.0 0

Index Scan using mbw_planejamentoestacaomonta_cbe4ebd5 on mbw_planejamentoestacaomonta mbw_planejamentoestacaomonta_9 (cost=0.28..2.07 rows=1 width=4) (never executed)

  • Index Cond: (estacao_monta_id = 44)
  • Filter: (categoria = 4)
31. 0.000 0.000 ↓ 0.0 0

Index Scan using mbw_planejamentoestacaomonta_cbe4ebd5 on mbw_planejamentoestacaomonta mbw_planejamentoestacaomonta_10 (cost=0.28..2.07 rows=1 width=4) (never executed)

  • Index Cond: (estacao_monta_id = 44)
  • Filter: (categoria = 5)
32. 0.000 0.000 ↓ 0.0 0

Index Scan using mbw_planejamentoestacaomonta_cbe4ebd5 on mbw_planejamentoestacaomonta mbw_planejamentoestacaomonta_11 (cost=0.28..2.07 rows=1 width=4) (never executed)

  • Index Cond: (estacao_monta_id = 44)
  • Filter: (categoria = 6)
33. 92.990 394,561.350 ↓ 3.6 12,264 1

Merge Right Join (cost=1,243,534.12..1,262,110.22 rows=3,416 width=150) (actual time=386,409.733..394,561.350 rows=12,264 loops=1)

  • Merge Cond: (aguardando_inseminacao.femea_id = r.femea_id)
34. 46.596 143,018.096 ↑ 2.6 260,216 1

Subquery Scan on aguardando_inseminacao (cost=1,243,454.60..1,260,083.37 rows=683,152 width=8) (actual time=142,732.584..143,018.096 rows=260,216 loops=1)

35. 97.103 142,971.500 ↑ 2.6 260,216 1

SetOp Except (cost=1,243,454.60..1,253,251.85 rows=683,152 width=11) (actual time=142,732.583..142,971.500 rows=260,216 loops=1)

36. 1,869.146 142,874.397 ↑ 3.0 324,269 1

Sort (cost=1,243,454.60..1,245,903.91 rows=979,725 width=11) (actual time=142,732.576..142,874.397 rows=324,269 loops=1)

  • Sort Key: "*SELECT* 1".femea_id, "*SELECT* 1".cobertura_id, "*SELECT* 1".data_parto
  • Sort Method: external merge Disk: 27000kB
37. 217.650 141,005.251 ↓ 1.1 1,109,392 1

Append (cost=0.86..1,136,154.88 rows=979,725 width=11) (actual time=10.496..141,005.251 rows=1,109,392 loops=1)

38. 256.015 138,129.883 ↓ 1.2 812,685 1

Subquery Scan on *SELECT* 1 (cost=0.86..747,407.20 rows=683,152 width=12) (actual time=10.496..138,129.883 rows=812,685 loops=1)

39. 676.766 137,873.868 ↓ 1.2 812,685 1

Nested Loop (cost=0.86..740,575.68 rows=683,152 width=12) (actual time=10.495..137,873.868 rows=812,685 loops=1)

  • Join Filter: (p.femea_id = vw.femea_id)
40. 191.073 50,022.652 ↓ 1.0 316,998 1

Nested Loop (cost=0.43..533,303.19 rows=313,030 width=12) (actual time=10.447..50,022.652 rows=316,998 loops=1)

41. 2,222.056 2,222.056 ↑ 1.0 319,527 1

Seq Scan on mbw_parto p (cost=0.00..92,519.61 rows=321,153 width=8) (actual time=8.964..2,222.056 rows=319,527 loops=1)

  • Filter: ((data_parto >= '2018-05-01'::date) AND (data_parto <= '2019-02-28'::date))
  • Rows Removed by Filter: 2191772
42. 47,609.523 47,609.523 ↑ 1.0 1 319,527

Index Scan using mbw_animal_pkey on mbw_animal a (cost=0.43..1.36 rows=1 width=4) (actual time=0.148..0.149 rows=1 loops=319,527)

  • Index Cond: (id = p.femea_id)
  • Filter: (data_morte IS NULL)
  • Rows Removed by Filter: 0
43. 87,174.450 87,174.450 ↓ 1.5 3 316,998

Index Scan using vw_modelo_reprodutivo_femea_id_idx on vw_modelo_reprodutivo vw (cost=0.43..0.64 rows=2 width=8) (actual time=0.111..0.275 rows=3 loops=316,998)

  • Index Cond: (femea_id = a.id)
  • Filter: (cobertura_situacao_nome = 'Parida'::text)
  • Rows Removed by Filter: 2
44. 59.461 2,657.718 ↓ 1.0 296,707 1

Subquery Scan on *SELECT* 2 (cost=146,820.70..388,747.68 rows=296,573 width=8) (actual time=1,448.538..2,657.718 rows=296,707 loops=1)

45. 1,174.256 2,598.257 ↓ 1.0 296,707 1

Bitmap Heap Scan on vw_modelo_reprodutivo vw1 (cost=146,820.70..385,781.95 rows=296,573 width=8) (actual time=1,448.536..2,598.257 rows=296,707 loops=1)

  • Recheck Cond: (data_hora_cobertura >= '2019-02-28'::date)
  • Rows Removed by Index Recheck: 2090073
  • Heap Blocks: exact=83591 lossy=69047
46. 1,424.001 1,424.001 ↓ 1.0 296,707 1

Bitmap Index Scan on vw_modelo_reprodutivo_data_hora_cobertura_idx (cost=0.00..146,746.56 rows=296,573 width=0) (actual time=1,424.001..1,424.001 rows=296,707 loops=1)

  • Index Cond: (data_hora_cobertura >= '2019-02-28'::date)
47. 7.574 251,450.264 ↓ 12,262.0 12,262 1

Materialize (cost=79.52..79.85 rows=1 width=146) (actual time=243,539.741..251,450.264 rows=12,262 loops=1)

48. 2,925.257 251,442.690 ↓ 4,767.0 4,767 1

Nested Loop Left Join (cost=79.52..79.85 rows=1 width=146) (actual time=243,539.731..251,442.690 rows=4,767 loops=1)

  • Join Filter: (vw_2.femea_id = r.femea_id)
  • Rows Removed by Join Filter: 22466923
49. 8.589 138,104.179 ↓ 4,767.0 4,767 1

Merge Left Join (cost=47.78..47.82 rows=1 width=134) (actual time=138,091.238..138,104.179 rows=4,767 loops=1)

  • Merge Cond: (r.femea_id = dias_medios_descanso.femea_id)
50. 4.535 73,417.707 ↓ 4,765.0 4,765 1

Sort (cost=0.03..0.04 rows=1 width=106) (actual time=73,415.784..73,417.707 rows=4,765 loops=1)

  • Sort Key: r.femea_id
  • Sort Method: quicksort Memory: 565kB
51. 73,413.172 73,413.172 ↓ 4,765.0 4,765 1

CTE Scan on resultado r (cost=0.00..0.02 rows=1 width=106) (actual time=73,407.926..73,413.172 rows=4,765 loops=1)

52. 4.183 64,677.883 ↓ 775.3 4,652 1

Sort (cost=47.75..47.76 rows=6 width=32) (actual time=64,675.446..64,677.883 rows=4,652 loops=1)

  • Sort Key: dias_medios_descanso.femea_id
  • Sort Method: quicksort Memory: 547kB
53. 0.881 64,673.700 ↓ 775.0 4,650 1

Subquery Scan on dias_medios_descanso (cost=47.49..47.67 rows=6 width=32) (actual time=64,671.474..64,673.700 rows=4,650 loops=1)

54. 16.992 64,672.819 ↓ 775.0 4,650 1

HashAggregate (cost=47.49..47.61 rows=6 width=32) (actual time=64,671.473..64,672.819 rows=4,650 loops=1)

  • Group Key: (date_part('day'::text, (c.data_hora_cobertura - (p_1.data_parto)::timestamp with time zone)))::integer, p_1.data_parto, vw_1.femea_id, c.data_hora_cobertura, c.data_hora_cobertura, c2.data_hora_cobertura, c3.data_hora_cobertura
55.          

CTE cobertura_posterior

56. 4.812 64,497.260 ↓ 7,292.0 7,292 1

WindowAgg (cost=42.34..42.36 rows=1 width=16) (actual time=64,491.367..64,497.260 rows=7,292 loops=1)

57. 4.473 64,492.448 ↓ 7,292.0 7,292 1

Sort (cost=42.34..42.35 rows=1 width=16) (actual time=64,491.354..64,492.448 rows=7,292 loops=1)

  • Sort Key: vw_5.femea_id, vw_5.data_hora_cobertura
  • Sort Method: quicksort Memory: 534kB
58. 2.461 64,487.975 ↓ 7,292.0 7,292 1

Nested Loop (cost=18.30..42.33 rows=1 width=16) (actual time=64,395.244..64,487.975 rows=7,292 loops=1)

59. 3.076 64,418.776 ↓ 476.7 4,767 1

Nested Loop (cost=17.86..35.43 rows=10 width=8) (actual time=64,395.212..64,418.776 rows=4,767 loops=1)

60. 4.108 64,396.632 ↓ 476.7 4,767 1

HashAggregate (cost=17.43..17.53 rows=10 width=4) (actual time=64,395.179..64,396.632 rows=4,767 loops=1)

  • Group Key: vw_6.femea_id
61. 1.190 64,392.524 ↓ 951.9 9,519 1

Append (cost=17.12..17.41 rows=10 width=4) (actual time=64,389.367..64,392.524 rows=9,519 loops=1)

62. 5.788 64,390.572 ↓ 4,714.0 4,714 1

HashAggregate (cost=17.12..17.13 rows=1 width=4) (actual time=64,389.366..64,390.572 rows=4,714 loops=1)

  • Group Key: vw_6.femea_id
63. 6.628 64,384.784 ↓ 7,420.0 7,420 1

Nested Loop (cost=0.99..17.11 rows=1 width=4) (actual time=64,303.221..64,384.784 rows=7,420 loops=1)

64. 4.160 64,348.476 ↓ 7,420.0 7,420 1

Nested Loop (cost=0.56..15.43 rows=1 width=8) (actual time=64,303.159..64,348.476 rows=7,420 loops=1)

65. 64,310.681 64,310.681 ↓ 533.9 4,805 1

CTE Scan on femeas_aptas f (cost=0.00..0.18 rows=9 width=4) (actual time=64,303.115..64,310.681 rows=4,805 loops=1)

66. 33.635 33.635 ↓ 2.0 2 4,805

Index Only Scan using vw_modelo_reprodutivo_data_hora_cobertura_idx on vw_modelo_reprodutivo vw_6 (cost=0.56..1.69 rows=1 width=4) (actual time=0.006..0.007 rows=2 loops=4,805)

  • Index Cond: ((femea_id = f.femea_id) AND (data_hora_cobertura >= '2018-10-20'::date) AND (data_hora_cobertura <= '2019-04-15'::date) AND (propriedade_id = 3232))
  • Filter: (categoria_femea <> 'Novilha'::text)
  • Heap Fetches: 0
67. 29.680 29.680 ↑ 1.0 1 7,420

Index Only Scan using mbw_animal_pkey on mbw_animal a_3 (cost=0.43..1.67 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=7,420)

  • Index Cond: (id = vw_6.femea_id)
  • Heap Fetches: 7420
68. 0.762 0.762 ↓ 533.9 4,805 1

CTE Scan on femeas_aptas (cost=0.00..0.18 rows=9 width=4) (actual time=0.002..0.762 rows=4,805 loops=1)

69. 19.068 19.068 ↑ 1.0 1 4,767

Index Only Scan using mbw_animal_pkey on mbw_animal a_2 (cost=0.43..1.77 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=4,767)

  • Index Cond: (id = vw_6.femea_id)
  • Heap Fetches: 4767
70. 66.738 66.738 ↓ 2.0 2 4,767

Index Scan using vw_modelo_reprodutivo_femea_id_idx on vw_modelo_reprodutivo vw_5 (cost=0.43..0.68 rows=1 width=16) (actual time=0.007..0.014 rows=2 loops=4,767)

  • Index Cond: (femea_id = a_2.id)
  • Filter: ((data_hora_cobertura >= '2018-10-20'::date) AND (data_hora_cobertura <= '2019-04-15'::date) AND (tipo_cobertura_id = 2) AND (propriedade_id = 3232))
  • Rows Removed by Filter: 4
71. 17.189 64,655.827 ↓ 4,182.7 25,096 1

Hash Left Join (cost=0.93..5.02 rows=6 width=32) (actual time=64,502.014..64,655.827 rows=25,096 loops=1)

  • Hash Cond: (vw_1.femea_id = c3.femea_id)
72. 9.096 64,637.581 ↓ 4,182.7 25,096 1

Hash Left Join (cost=0.90..4.89 rows=6 width=24) (actual time=64,500.925..64,637.581 rows=25,096 loops=1)

  • Hash Cond: (vw_1.femea_id = c2.femea_id)
73. 24.965 64,618.985 ↓ 4,182.7 25,096 1

Nested Loop Left Join (cost=0.86..4.83 rows=6 width=16) (actual time=64,491.409..64,618.985 rows=25,096 loops=1)

74. 6.319 64,518.756 ↓ 4,181.3 25,088 1

Nested Loop (cost=0.43..1.72 rows=6 width=12) (actual time=64,491.385..64,518.756 rows=25,088 loops=1)

75. 64,493.841 64,493.841 ↓ 4,649.0 4,649 1

CTE Scan on cobertura_posterior c (cost=0.00..0.02 rows=1 width=12) (actual time=64,491.374..64,493.841 rows=4,649 loops=1)

  • Filter: (ordem = 1)
  • Rows Removed by Filter: 2643
76. 18.596 18.596 ↑ 1.2 5 4,649

Index Only Scan using vw_modelo_reprodutivo_femea_id_idx on vw_modelo_reprodutivo vw_1 (cost=0.43..1.64 rows=6 width=4) (actual time=0.002..0.004 rows=5 loops=4,649)

  • Index Cond: (femea_id = c.femea_id)
  • Heap Fetches: 0
77. 75.264 75.264 ↑ 1.0 1 25,088

Index Scan using mbw_parto_24f7cd40 on mbw_parto p_1 (cost=0.43..0.51 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=25,088)

  • Index Cond: (vw_1.femea_id = femea_id)
  • Filter: ((data_parto >= '2018-05-01'::date) AND (data_parto <= '2019-02-28'::date))
  • Rows Removed by Filter: 2
78. 0.423 9.500 ↓ 1,870.0 1,870 1

Hash (cost=0.02..0.02 rows=1 width=12) (actual time=9.500..9.500 rows=1,870 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 97kB
79. 9.077 9.077 ↓ 1,870.0 1,870 1

CTE Scan on cobertura_posterior c2 (cost=0.00..0.02 rows=1 width=12) (actual time=0.011..9.077 rows=1,870 loops=1)

  • Filter: (ordem = 2)
  • Rows Removed by Filter: 5422
80. 0.107 1.057 ↓ 604.0 604 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 34kB
81. 0.950 0.950 ↓ 604.0 604 1

CTE Scan on cobertura_posterior c3 (cost=0.00..0.02 rows=1 width=12) (actual time=0.008..0.950 rows=604 loops=1)

  • Filter: (ordem = 3)
  • Rows Removed by Filter: 6688
82. 5,065.742 110,413.254 ↓ 785.7 4,714 4,767

HashAggregate (cost=31.74..31.89 rows=6 width=46) (actual time=22.120..23.162 rows=4,714 loops=4,767)

  • Group Key: vw_2.femea_id, vw_2.femea_identificacao_usual, (dg.data_exame - (dg.data_hora_cobertura)::date), (dg2.data_exame - (dg2.data_hora_cobertura)::date), (dg3.data_exame - (dg3.data_hora_cobertura)::date)
83.          

CTE ordem_dg

84. 8.193 453.204 ↓ 10,969.0 10,969 1

WindowAgg (cost=23.06..23.08 rows=1 width=36) (actual time=443.890..453.204 rows=10,969 loops=1)

85. 5.723 445.011 ↓ 10,969.0 10,969 1

Sort (cost=23.06..23.06 rows=1 width=36) (actual time=443.883..445.011 rows=10,969 loops=1)

  • Sort Key: vw_7.femea_id, vw_7.cobertura_id, re.data_exame
  • Sort Method: quicksort Memory: 1242kB
86. 8.597 439.288 ↓ 10,969.0 10,969 1

WindowAgg (cost=23.03..23.05 rows=1 width=36) (actual time=428.834..439.288 rows=10,969 loops=1)

87. 10.701 430.691 ↓ 10,969.0 10,969 1

Sort (cost=23.03..23.03 rows=1 width=36) (actual time=428.821..430.691 rows=10,969 loops=1)

  • Sort Key: vw_7.femea_id, vw_7.data_hora_cobertura
  • Sort Method: quicksort Memory: 1241kB
88. 17.363 419.990 ↓ 10,969.0 10,969 1

Nested Loop Left Join (cost=1.30..23.02 rows=1 width=36) (actual time=1.937..419.990 rows=10,969 loops=1)

  • Join Filter: ((vw_7.data_hora_cobertura >= '2018-10-20'::date) AND (vw_7.data_hora_cobertura <= '2019-04-15'::date))
89. 5.029 142.927 ↓ 7,420.0 7,420 1

Nested Loop (cost=0.86..22.36 rows=1 width=22) (actual time=0.063..142.927 rows=7,420 loops=1)

90. 5.996 41.798 ↓ 533.9 4,805 1

Nested Loop (cost=0.43..16.42 rows=9 width=8) (actual time=0.031..41.798 rows=4,805 loops=1)

91. 2.167 2.167 ↓ 533.9 4,805 1

CTE Scan on femeas_aptas f_1 (cost=0.00..0.18 rows=9 width=4) (actual time=0.002..2.167 rows=4,805 loops=1)

92. 33.635 33.635 ↑ 1.0 1 4,805

Index Only Scan using mbw_animal_pkey on mbw_animal e (cost=0.43..1.79 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=4,805)

  • Index Cond: (id = f_1.femea_id)
  • Heap Fetches: 4805
93. 96.100 96.100 ↓ 2.0 2 4,805

Index Scan using vw_modelo_reprodutivo_femea_id_idx on vw_modelo_reprodutivo vw_7 (cost=0.43..0.65 rows=1 width=22) (actual time=0.011..0.020 rows=2 loops=4,805)

  • Index Cond: (femea_id = e.id)
  • Filter: ((data_hora_cobertura >= '2018-10-20'::date) AND (data_hora_cobertura <= '2019-04-15'::date))
  • Rows Removed by Filter: 4
94. 259.700 259.700 ↑ 1.0 1 7,420

Index Scan using mbw_resultadodiagnosticogestacao_33024c41 on mbw_resultadodiagnosticogestacao re (cost=0.43..0.64 rows=1 width=14) (actual time=0.034..0.035 rows=1 loops=7,420)

  • Index Cond: (vw_7.cobertura_id = cobertura_id)
95. 2,108.190 105,347.512 ↓ 4,253.0 25,518 1

Nested Loop Left Join (cost=0.43..8.58 rows=6 width=46) (actual time=461.847..105,347.512 rows=25,518 loops=1)

  • Join Filter: (dg3.femea_id = vw_2.femea_id)
  • Rows Removed by Join Filter: 15434271
96. 5,999.521 59,935.276 ↓ 4,253.0 25,518 1

Nested Loop Left Join (cost=0.43..8.34 rows=6 width=34) (actual time=460.060..59,935.276 rows=25,518 loops=1)

  • Join Filter: (dg2.femea_id = vw_2.femea_id)
  • Rows Removed by Join Filter: 47834704
97. 30.927 730.725 ↓ 4,253.0 25,518 1

Nested Loop (cost=0.43..8.19 rows=6 width=22) (actual time=443.938..730.725 rows=25,518 loops=1)

98. 454.670 454.670 ↓ 4,714.0 4,714 1

CTE Scan on ordem_dg dg (cost=0.00..0.03 rows=1 width=16) (actual time=443.904..454.670 rows=4,714 loops=1)

  • Filter: ((ordem = 1) AND (ordem_dg = 1))
  • Rows Removed by Filter: 6255
99. 245.128 245.128 ↑ 1.2 5 4,714

Index Scan using vw_modelo_reprodutivo_femea_id_idx on vw_modelo_reprodutivo vw_2 (cost=0.43..8.11 rows=6 width=10) (actual time=0.019..0.052 rows=5 loops=4,714)

  • Index Cond: (femea_id = dg.femea_id)
100. 53,205.030 53,205.030 ↓ 1,875.0 1,875 25,518

CTE Scan on ordem_dg dg2 (cost=0.00..0.03 rows=1 width=16) (actual time=0.003..2.085 rows=1,875 loops=25,518)

  • Filter: ((ordem = 2) AND (ordem_dg = 1))
  • Rows Removed by Filter: 9094
101. 43,304.046 43,304.046 ↓ 605.0 605 25,518

CTE Scan on ordem_dg dg3 (cost=0.00..0.03 rows=1 width=16) (actual time=0.004..1.697 rows=605 loops=25,518)

  • Filter: ((ordem = 3) AND (ordem_dg = 1))
  • Rows Removed by Filter: 10364
Planning time : 24.397 ms
Execution time : 394,599.594 ms