explain.depesz.com

PostgreSQL's explain analyze made readable

Result: u9dL

Settings
# exclusive inclusive rows x rows loops node
1. 15.475 36,698.675 ↑ 1.0 1 1

Aggregate (cost=366,324.64..366,324.65 rows=1 width=128) (actual time=36,698.675..36,698.675 rows=1 loops=1)

2.          

CTE femeas_paridas_antes_em

3. 25.021 383.619 ↓ 24.9 33,935 1

WindowAgg (cost=183,045.06..183,072.30 rows=1,362 width=33) (actual time=352.546..383.619 rows=33,935 loops=1)

4. 27.631 358.598 ↓ 24.9 33,935 1

Sort (cost=183,045.06..183,048.46 rows=1,362 width=25) (actual time=352.527..358.598 rows=33,935 loops=1)

  • Sort Key: vw.femea_id, vw.data_hora_cobertura DESC
  • Sort Method: quicksort Memory: 4155kB
5. 330.967 330.967 ↓ 24.9 33,935 1

Index Scan using vw_modelo_reprodutivo_parto_id_idx2 on vw_modelo_reprodutivo vw (cost=0.56..182,974.16 rows=1,362 width=25) (actual time=48.165..330.967 rows=33,935 loops=1)

  • Index Cond: (propriedade_id = 3162)
  • Filter: ((data_hora_cobertura < '2019-11-10 00:00:00-03'::timestamp with time zone) AND (contrato_id = 16))
  • Rows Removed by Filter: 4645
6.          

CTE primeira_ia

7. 46.899 28,465.543 ↓ 12.0 4,645 1

WindowAgg (cost=0.56..182,539.34 rows=386 width=57) (actual time=442.222..28,465.543 rows=4,645 loops=1)

8. 1,536.792 28,418.644 ↓ 12.0 4,645 1

Nested Loop Left Join (cost=0.56..182,527.76 rows=386 width=45) (actual time=397.231..28,418.644 rows=4,645 loops=1)

  • Join Filter: (vw_1.femea_id = fp.femea_id)
  • Rows Removed by Join Filter: 17035137
9. 447.157 447.157 ↓ 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=44.671..447.157 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 = 3162))
  • Filter: (tipo_cobertura = 'IA'::text)
10. 26,434.695 26,434.695 ↓ 3,668.0 3,668 4,645

CTE Scan on femeas_paridas_antes_em fp (cost=0.00..34.05 rows=1 width=8) (actual time=0.077..5.691 rows=3,668 loops=4,645)

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

CTE cobeturas_anteriores

12. 7.139 31.108 ↓ 11,924.0 11,924 1

WindowAgg (cost=685.30..685.32 rows=1 width=20) (actual time=23.141..31.108 rows=11,924 loops=1)

13. 5.204 23.969 ↓ 11,924.0 11,924 1

Sort (cost=685.30..685.30 rows=1 width=12) (actual time=23.116..23.969 rows=11,924 loops=1)

  • Sort Key: vw_2.femea_id, vw_2.data_hora_cobertura DESC
  • Sort Method: quicksort Memory: 943kB
14. 3.874 18.765 ↓ 11,924.0 11,924 1

Nested Loop (cost=0.56..685.29 rows=1 width=12) (actual time=0.028..18.765 rows=11,924 loops=1)

15. 0.956 0.956 ↓ 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..0.956 rows=4,645 loops=1)

16. 13.935 13.935 ↓ 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.75 rows=1 width=12) (actual time=0.002..0.003 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 = 3162) AND (contrato_id = 16))
  • Heap Fetches: 0
17.          

Initplan (for Aggregate)

18. 0.004 0.004 ↑ 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.004..0.004 rows=1 loops=1)

  • Index Cond: (contrato_id = 16)
19. 0.027 0.027 ↑ 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=0.026..0.027 rows=1 loops=1)

  • Index Cond: (contrato_id = 16)
20. 1,272.888 36,683.169 ↓ 4,645.0 4,645 1

Nested Loop Left Join (cost=9.14..22.86 rows=1 width=56) (actual time=28,523.157..36,683.169 rows=4,645 loops=1)

  • Join Filter: (ant.femea_id = p.femea_id)
  • Rows Removed by Join Filter: 14484636
21. 3.271 28,493.876 ↓ 4,645.0 4,645 1

Hash Join (cost=9.14..22.83 rows=1 width=60) (actual time=28,490.584..28,493.876 rows=4,645 loops=1)

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

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

23. 0.025 0.025 ↑ 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.019..0.025 rows=1 loops=1)

  • Index Cond: (propriedade_id = 3162)
  • Filter: (monta_fechada IS FALSE)
  • Rows Removed by Filter: 2
24. 0.035 0.035 ↑ 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=0.011..0.035 rows=6 loops=1)

  • Index Cond: (estacao_monta_id = em.id)
25. 10.444 28,490.536 ↓ 2,322.5 4,645 1

Hash (cost=8.69..8.69 rows=2 width=60) (actual time=28,490.535..28,490.536 rows=4,645 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 358kB
26. 28,480.092 28,480.092 ↓ 2,322.5 4,645 1

CTE Scan on primeira_ia p (cost=0.00..8.69 rows=2 width=60) (actual time=442.226..28,480.092 rows=4,645 loops=1)

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

CTE Scan on cobeturas_anteriores ant (cost=0.00..0.02 rows=1 width=4) (actual time=0.005..1.489 rows=3,119 loops=4,645)

  • Filter: (ordem = 1)
  • Rows Removed by Filter: 8805
Planning time : 5.382 ms
Execution time : 36,700.132 ms