explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SNq7

Settings
# exclusive inclusive rows x rows loops node
1. 8.373 17,891.180 ↓ 27.0 13,277 1

Hash Left Join (cost=275,358.22..275,370.08 rows=491 width=140) (actual time=17,801.986..17,891.180 rows=13,277 loops=1)

  • Hash Cond: (a.animal_id = v.femea_id)
2.          

CTE estoque

3. 14,680.782 14,680.782 ↓ 16.0 15,957 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=14,678.649..14,680.782 rows=15,957 loops=1)

4.          

CTE animais_view

5. 39.235 3,105.647 ↓ 3,290.0 26,320 1

Hash Join (cost=257,987.61..267,103.55 rows=8 width=13) (actual time=2,983.583..3,105.647 rows=26,320 loops=1)

  • Hash Cond: ((b.femea_id = v_2.femea_id) AND (b.cobertura_mais_recente = v_2.data_hora_cobertura))
6.          

CTE base

7. 34.232 1,649.329 ↑ 6.4 26,296 1

GroupAggregate (cost=138,396.12..141,545.75 rows=168,663 width=12) (actual time=1,575.373..1,649.329 rows=26,296 loops=1)

  • Group Key: v_1.femea_id
8. 125.143 1,615.097 ↑ 1.0 193,963 1

Sort (cost=138,396.12..138,883.79 rows=195,067 width=12) (actual time=1,575.354..1,615.097 rows=193,963 loops=1)

  • Sort Key: v_1.femea_id
  • Sort Method: external merge Disk: 4,952kB
9. 1,486.260 1,489.954 ↑ 1.0 193,963 1

Bitmap Heap Scan on vw_modelo_reprodutivo v_1 (cost=85.80..117,920.46 rows=195,067 width=12) (actual time=11.939..1,489.954 rows=193,963 loops=1)

  • Recheck Cond: ((data_hora_cobertura <= '2020-08-03'::date) AND (contrato_id = 102))
  • Rows Removed by Index Recheck: 2,413,000
  • Heap Blocks: lossy=84,549
10. 3.694 3.694 ↑ 1.9 846,080 1

Bitmap Index Scan on vw_modelo_reprodutivo_contrato_cobertura_brin (cost=0.00..37.04 rows=1,606,482 width=0) (actual time=3.694..3.694 rows=846,080 loops=1)

  • Index Cond: ((data_hora_cobertura <= '2020-08-03'::date) AND (contrato_id = 102))
11. 1,658.338 1,658.338 ↑ 6.4 26,296 1

CTE Scan on base b (cost=0.00..3,373.26 rows=168,663 width=12) (actual time=1,575.376..1,658.338 rows=26,296 loops=1)

12. 70.517 1,408.074 ↑ 1.0 193,963 1

Hash (cost=112,372.85..112,372.85 rows=195,067 width=21) (actual time=1,408.074..1,408.074 rows=193,963 loops=1)

  • Buckets: 65,536 Batches: 4 Memory Usage: 3,230kB
13. 1,333.900 1,337.557 ↑ 1.0 193,963 1

Bitmap Heap Scan on vw_modelo_reprodutivo v_2 (cost=85.80..112,372.85 rows=195,067 width=21) (actual time=10.677..1,337.557 rows=193,963 loops=1)

  • Recheck Cond: (contrato_id = 102)
  • Rows Removed by Index Recheck: 2,413,000
  • Heap Blocks: lossy=84,549
14. 3.657 3.657 ↑ 1.9 846,080 1

Bitmap Index Scan on vw_modelo_reprodutivo_contrato_cobertura_brin (cost=0.00..37.04 rows=1,606,482 width=0) (actual time=3.657..3.657 rows=846,080 loops=1)

  • Index Cond: (contrato_id = 102)
15.          

CTE animais

16. 18.040 14,751.852 ↓ 27.0 13,271 1

Nested Loop (cost=0.43..8,244.16 rows=491 width=140) (actual time=14,678.688..14,751.852 rows=13,271 loops=1)

17. 14,685.941 14,685.941 ↓ 16.0 15,957 1

CTE Scan on estoque e_1 (cost=0.00..20.00 rows=1,000 width=108) (actual time=14,678.651..14,685.941 rows=15,957 loops=1)

18. 47.871 47.871 ↑ 1.0 1 15,957

Index Scan using mbw_animal_pkey on mbw_animal a_1 (cost=0.43..8.22 rows=1 width=6) (actual time=0.003..0.003 rows=1 loops=15,957)

  • Index Cond: (id = e_1.animal_id)
  • Filter: (tipo_id = ANY ('{1,2,3}'::integer[]))
  • Rows Removed by Filter: 0
19. 14,759.527 14,759.527 ↓ 27.0 13,271 1

CTE Scan on animais a (cost=0.00..9.82 rows=491 width=140) (actual time=14,678.691..14,759.527 rows=13,271 loops=1)

20. 6.251 3,123.280 ↓ 3,290.0 26,320 1

Hash (cost=0.16..0.16 rows=8 width=36) (actual time=3,123.280..3,123.280 rows=26,320 loops=1)

  • Buckets: 32,768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1,468kB
21. 3,117.029 3,117.029 ↓ 3,290.0 26,320 1

CTE Scan on animais_view v (cost=0.00..0.16 rows=8 width=36) (actual time=2,983.588..3,117.029 rows=26,320 loops=1)

Planning time : 0.963 ms
Execution time : 17,896.535 ms