explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2lTU

Settings
# exclusive inclusive rows x rows loops node
1. 17.944 76,235.663 ↓ 314.0 3,454 1

HashAggregate (cost=1,094,676.07..1,094,676.34 rows=11 width=79) (actual time=76,233.778..76,235.663 rows=3,454 loops=1)

  • Group Key: vw.cobertura_id, vw.femea_identificacao_usual, vw.macho_raca_nome, vw.cobertura_situacao_nome, vw.tipo_cobertura, (vw.data_hora_cobertura)::timestamp without time zone, vw.resultado_parto_nome, vw.situacao_cria, vw.parto_id, vw.data_parto, vw.propriedade_id, vw.dg_inicial, ((vw.data_hora_cobertura + '295 days'::interval))::timestamp without time zone, (date_part('day'::text, (vw.data_hora_cobertura + '295 days'::interval)))::integer, a.sexo, p.propriedade_id, pc.cria_id
2.          

CTE animais

3. 165.111 182.434 ↑ 1.1 132,459 1

Bitmap Heap Scan on mbw_animal a_1 (cost=3,189.84..253,538.42 rows=147,794 width=661) (actual time=19.292..182.434 rows=132,459 loops=1)

  • Recheck Cond: (contrato_id = 16)
  • Heap Blocks: exact=9476
4. 17.323 17.323 ↓ 1.3 191,468 1

Bitmap Index Scan on mbw_animal_868819a8 (cost=0.00..3,152.89 rows=147,794 width=0) (actual time=17.323..17.323 rows=191,468 loops=1)

  • Index Cond: (contrato_id = 16)
5.          

CTE propriedade_na_data

6. 189.717 954.898 ↑ 2.2 132,459 1

Hash Left Join (cost=46,708.64..62,334.16 rows=291,154 width=16) (actual time=279.217..954.898 rows=132,459 loops=1)

  • Hash Cond: (a_2.id = r1.animal_id)
7.          

CTE resultado

8. 62.060 191.025 ↑ 1.0 78,076 1

WindowAgg (cost=43,353.74..44,930.20 rows=78,823 width=12) (actual time=116.851..191.025 rows=78,076 loops=1)

9. 56.635 128.965 ↑ 1.0 78,076 1

Sort (cost=43,353.74..43,550.79 rows=78,823 width=12) (actual time=116.839..128.965 rows=78,076 loops=1)

  • Sort Key: p_1.animal_id, p_1.data DESC
  • Sort Method: quicksort Memory: 6732kB
10. 64.188 72.330 ↑ 1.0 78,076 1

Bitmap Heap Scan on mbw_transferenciapropriedade p_1 (cost=1,483.35..36,942.93 rows=78,823 width=12) (actual time=8.493..72.330 rows=78,076 loops=1)

  • Recheck Cond: (contrato_id = 16)
  • Filter: (data <= ('now'::cstring)::date)
  • Heap Blocks: exact=1995
11. 8.142 8.142 ↓ 1.2 97,101 1

Bitmap Index Scan on mbw_transferenciapropriedade_868819a8 (cost=0.00..1,463.64 rows=78,829 width=0) (actual time=8.142..8.142 rows=97,101 loops=1)

  • Index Cond: (contrato_id = 16)
12. 505.288 505.288 ↑ 1.1 132,459 1

CTE Scan on animais a_2 (cost=0.00..2,955.88 rows=147,794 width=12) (actual time=19.299..505.288 rows=132,459 loops=1)

13. 17.886 259.893 ↓ 180.7 71,187 1

Hash (cost=1,773.52..1,773.52 rows=394 width=8) (actual time=259.893..259.893 rows=71,187 loops=1)

  • Buckets: 131072 (originally 1024) Batches: 1 (originally 1) Memory Usage: 3805kB
14. 242.007 242.007 ↓ 180.7 71,187 1

CTE Scan on resultado r1 (cost=0.00..1,773.52 rows=394 width=8) (actual time=116.858..242.007 rows=71,187 loops=1)

  • Filter: (row_number = 1)
  • Rows Removed by Filter: 6889
15. 21.521 76,217.719 ↓ 319.0 3,509 1

Nested Loop Left Join (cost=653,520.55..778,803.02 rows=11 width=79) (actual time=69,342.528..76,217.719 rows=3,509 loops=1)

16. 7.943 76,101.455 ↓ 319.0 3,509 1

Nested Loop Left Join (cost=653,520.12..778,791.18 rows=11 width=77) (actual time=69,340.951..76,101.455 rows=3,509 loops=1)

17. 9.793 76,000.740 ↓ 312.4 3,436 1

Nested Loop Left Join (cost=653,519.69..778,785.28 rows=11 width=77) (actual time=69,339.605..76,000.740 rows=3,436 loops=1)

18. 14.383 75,891.303 ↓ 312.4 3,436 1

Hash Join (cost=653,519.26..778,692.25 rows=11 width=69) (actual time=69,338.048..75,891.303 rows=3,436 loops=1)

  • Hash Cond: (vw.femea_id = pr.animal_id)
19. 6,554.925 74,734.886 ↓ 101.1 3,436 1

Bitmap Heap Scan on vw_modelo_reprodutivo vw (cost=644,056.76..767,992.16 rows=34 width=73) (actual time=68,192.742..74,734.886 rows=3,436 loops=1)

  • Recheck Cond: (propriedade_id = 3144)
  • Filter: ((dg_inicial = 'Positivo'::text) AND (((cobertura_situacao_nome = 'Parida'::text) AND (situacao_cria = 'Vivo'::text)) OR (cobertura_situacao_nome = 'Gestante'::text)) AND ((((data_hora_cobertura + '270 days'::interval day) >= '2017-12-01 00:00:00-02'::timestamp with time zone) AND ((data_hora_cobertura + '270 days'::interval day) <= '2018-12-01 00:00:00-02'::timestamp with time zone)) OR (((data_hora_cobertura + '315 days'::interval day) >= '2017-12-01 00:00:00-02'::timestamp with time zone) AND ((data_hora_cobertura + '315 days'::interval day) <= '2018-12-01 00:00:00-02'::timestamp with time zone))))
  • Rows Removed by Filter: 22865
  • Heap Blocks: exact=25081
20. 68,179.961 68,179.961 ↑ 1.5 26,301 1

Bitmap Index Scan on vw_modelo_reprodutivo_data_hora_cobertura_idx (cost=0.00..644,056.75 rows=40,231 width=0) (actual time=68,179.961..68,179.961 rows=26,301 loops=1)

  • Index Cond: (propriedade_id = 3144)
21. 56.952 1,142.034 ↑ 2.2 132,459 1

Hash (cost=5,823.08..5,823.08 rows=291,154 width=4) (actual time=1,142.034..1,142.034 rows=132,459 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 8753kB
22. 1,085.082 1,085.082 ↑ 2.2 132,459 1

CTE Scan on propriedade_na_data pr (cost=0.00..5,823.08 rows=291,154 width=4) (actual time=279.222..1,085.082 rows=132,459 loops=1)

23. 99.644 99.644 ↑ 1.0 1 3,436

Index Scan using mbw_parto_pkey on mbw_parto p (cost=0.43..8.45 rows=1 width=8) (actual time=0.028..0.029 rows=1 loops=3,436)

  • Index Cond: (vw.parto_id = id)
24. 92.772 92.772 ↑ 1.0 1 3,436

Index Scan using mbw_partocria_6e4499ea on mbw_partocria pc (cost=0.43..0.53 rows=1 width=8) (actual time=0.027..0.027 rows=1 loops=3,436)

  • Index Cond: (parto_id = p.id)
25. 94.743 94.743 ↑ 1.0 1 3,509

Index Scan using mbw_animal_pkey on mbw_animal a (cost=0.43..1.05 rows=1 width=6) (actual time=0.026..0.027 rows=1 loops=3,509)

  • Index Cond: (id = pc.cria_id)
Planning time : 33.852 ms
Execution time : 76,267.572 ms