explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LP6P

Settings
# exclusive inclusive rows x rows loops node
1. 22.748 98,757.934 ↑ 1.0 1 1

Aggregate (cost=370,797.12..370,797.13 rows=1 width=128) (actual time=98,757.934..98,757.934 rows=1 loops=1)

2.          

CTE femeas_paridas_antes_em

3. 35.667 42,599.297 ↑ 1.1 33,935 1

WindowAgg (cost=185,835.32..186,612.06 rows=38,837 width=33) (actual time=42,555.376..42,599.297 rows=33,935 loops=1)

4. 83.477 42,563.630 ↑ 1.1 33,935 1

Sort (cost=185,835.32..185,932.41 rows=38,837 width=25) (actual time=42,555.355..42,563.630 rows=33,935 loops=1)

  • Sort Key: vw.femea_id, vw.data_hora_cobertura DESC
  • Sort Method: quicksort Memory: 4,155kB
5. 42,480.153 42,480.153 ↑ 1.1 33,935 1

Index Scan using vw_modelo_reprodutivo_parto_id_idx2 on vw_modelo_reprodutivo vw (cost=0.56..182,874.94 rows=38,837 width=25) (actual time=4,854.629..42,480.153 rows=33,935 loops=1)

  • Index Cond: (propriedade_id = 3,162)
  • Filter: (data_hora_cobertura < '2019-11-10 00:00:00-03'::timestamp with time zone)
  • Rows Removed by Filter: 4,645
6.          

CTE primeira_ia

7. 52.973 89,879.405 ↓ 12.0 4,645 1

WindowAgg (cost=0.56..183,476.22 rows=386 width=57) (actual time=45,491.502..89,879.405 rows=4,645 loops=1)

8. 1,587.735 89,826.432 ↓ 12.0 4,645 1

Nested Loop Left Join (cost=0.56..183,464.64 rows=386 width=45) (actual time=45,424.927..89,826.432 rows=4,645 loops=1)

  • Join Filter: (vw_1.femea_id = fp.femea_id)
  • Rows Removed by Join Filter: 17,035,137
9. 18,656.597 18,656.597 ↓ 12.0 4,645 1

Index Scan using vw_modelo_reprodutivo_data_hora_cobertura_idx on vw_modelo_reprodutivo vw_1 (cost=0.56..182,485.04 rows=386 width=41) (actual time=2,869.533..18,656.597 rows=4,645 loops=1)

  • Index Cond: ((data_hora_cobertura >= '2019-11-10 00:00:00-03'::timestamp with time zone) AND (data_hora_cobertura <= '2020-03-15 00:00:00-03'::timestamp with time zone) AND (propriedade_id = 3,162))
  • Filter: (tipo_cobertura = 'IA'::text)
10. 69,582.100 69,582.100 ↓ 3,668.0 3,668 4,645

CTE Scan on femeas_paridas_antes_em fp (cost=0.00..970.93 rows=1 width=8) (actual time=9.162..14.980 rows=3,668 loops=4,645)

  • Filter: ((ordem = 1) AND (situacao_cria = 'Vivo'::text))
  • Rows Removed by Filter: 30,267
11.          

CTE cobeturas_anteriores

12. 12.638 66.219 ↓ 993.7 11,924 1

WindowAgg (cost=680.68..680.92 rows=12 width=20) (actual time=51.439..66.219 rows=11,924 loops=1)

13. 9.761 53.581 ↓ 993.7 11,924 1

Sort (cost=680.68..680.71 rows=12 width=12) (actual time=51.422..53.581 rows=11,924 loops=1)

  • Sort Key: vw_2.femea_id, vw_2.data_hora_cobertura DESC
  • Sort Method: quicksort Memory: 943kB
14. 4.970 43.820 ↓ 993.7 11,924 1

Nested Loop (cost=0.56..680.46 rows=12 width=12) (actual time=2.626..43.820 rows=11,924 loops=1)

15. 1.690 1.690 ↓ 12.0 4,645 1

CTE Scan on primeira_ia p_1 (cost=0.00..7.72 rows=386 width=4) (actual time=0.001..1.690 rows=4,645 loops=1)

16. 37.160 37.160 ↓ 3.0 3 4,645

Index Only Scan using vw_modelo_reprodutivo_data_hora_cobertura_idx on vw_modelo_reprodutivo vw_2 (cost=0.56..1.73 rows=1 width=12) (actual time=0.006..0.008 rows=3 loops=4,645)

  • Index Cond: ((femea_id = p_1.femea_id) AND (data_hora_cobertura <= '2019-11-10 00:00:00-03'::timestamp with time zone) AND (propriedade_id = 3,162))
  • Heap Fetches: 0
17.          

Initplan (for Aggregate)

18. 0.012 0.012 ↑ 1.0 1 1

Index Scan using mbw_configuracoesgerais_868819a8 on mbw_configuracoesgerais cfg (cost=0.15..2.37 rows=1 width=4) (actual time=0.012..0.012 rows=1 loops=1)

  • Index Cond: (contrato_id = 16)
19. 1.192 1.192 ↑ 1.0 1 1

Index Scan using mbw_configuracoesgerais_868819a8 on mbw_configuracoesgerais cfg_1 (cost=0.15..2.37 rows=1 width=4) (actual time=1.190..1.192 rows=1 loops=1)

  • Index Cond: (contrato_id = 16)
20. 1,298.322 98,733.982 ↓ 4,645.0 4,645 1

Nested Loop Left Join (cost=9.14..23.11 rows=1 width=56) (actual time=89,980.330..98,733.982 rows=4,645 loops=1)

  • Join Filter: (ant.femea_id = p.femea_id)
  • Rows Removed by Join Filter: 14,484,636
21. 4.077 89,915.405 ↓ 4,645.0 4,645 1

Hash Join (cost=9.14..22.83 rows=1 width=60) (actual time=89,911.314..89,915.405 rows=4,645 loops=1)

  • Hash Cond: (pl.categoria = p.categoria_femea_id)
22. 0.011 3.095 ↑ 1.7 6 1

Nested Loop (cost=0.43..14.07 rows=10 width=6) (actual time=3.055..3.095 rows=6 loops=1)

23. 0.769 0.769 ↑ 2.0 1 1

Index Scan using mbw_estacaomonta_62d4a177 on mbw_estacaomonta em (cost=0.15..4.20 rows=2 width=4) (actual time=0.761..0.769 rows=1 loops=1)

  • Index Cond: (propriedade_id = 3,162)
  • Filter: (monta_fechada IS FALSE)
  • Rows Removed by Filter: 2
24. 2.315 2.315 ↑ 1.0 6 1

Index Scan using mbw_planejamentoestacaomonta_cbe4ebd5 on mbw_planejamentoestacaomonta pl (cost=0.28..4.87 rows=6 width=10) (actual time=2.287..2.315 rows=6 loops=1)

  • Index Cond: (estacao_monta_id = em.id)
25. 12.818 89,908.233 ↓ 2,322.5 4,645 1

Hash (cost=8.69..8.69 rows=2 width=60) (actual time=89,908.233..89,908.233 rows=4,645 loops=1)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 358kB
26. 89,895.415 89,895.415 ↓ 2,322.5 4,645 1

CTE Scan on primeira_ia p (cost=0.00..8.69 rows=2 width=60) (actual time=45,491.508..89,895.415 rows=4,645 loops=1)

  • Filter: (ordem = 1)
27. 7,520.255 7,520.255 ↓ 3,119.0 3,119 4,645

CTE Scan on cobeturas_anteriores ant (cost=0.00..0.27 rows=1 width=4) (actual time=0.011..1.619 rows=3,119 loops=4,645)

  • Filter: (ordem = 1)
  • Rows Removed by Filter: 8,805
Planning time : 3.584 ms
Execution time : 98,759.352 ms