explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nFty

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 8.881 19,145.537 ↓ 27.7 13,277 1

Hash Left Join (cost=487,622.75..487,636.56 rows=479 width=140) (actual time=19,058.510..19,145.537 rows=13,277 loops=1)

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

CTE estoque

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

4.          

CTE animais_view

5. 309.813 4,911.100 ↓ 258.0 26,320 1

Hash Join (cost=456,536.54..479,345.14 rows=102 width=13) (actual time=4,520.313..4,911.100 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.644 2,145.543 ↑ 6.0 26,296 1

GroupAggregate (cost=213,881.72..216,833.29 rows=158,309 width=12) (actual time=2,073.341..2,145.543 rows=26,296 loops=1)

  • Group Key: v_1.femea_id
8. 123.378 2,110.899 ↓ 1.1 193,963 1

Sort (cost=213,881.72..214,337.88 rows=182,463 width=12) (actual time=2,073.325..2,110.899 rows=193,963 loops=1)

  • Sort Key: v_1.femea_id
  • Sort Method: external merge Disk: 4,952kB
9. 1,979.545 1,987.521 ↓ 1.1 193,963 1

Bitmap Heap Scan on vw_modelo_reprodutivo v_1 (cost=104.12..194,818.47 rows=182,463 width=12) (actual time=8.727..1,987.521 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=158,995
10. 7.976 7.976 ↓ 1.1 1,591,040 1

Bitmap Index Scan on vw_modelo_reprodutivo_contrato_cobertura_brin (cost=0.00..58.50 rows=1,479,732 width=0) (actual time=7.976..7.976 rows=1,591,040 loops=1)

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

CTE Scan on base b (cost=0.00..3,166.18 rows=158,309 width=12) (actual time=2,073.344..2,155.203 rows=26,296 loops=1)

12. 844.199 2,446.084 ↑ 1.0 2,606,963 1

Hash (cost=185,148.30..185,148.30 rows=2,615,330 width=21) (actual time=2,446.084..2,446.084 rows=2,606,963 loops=1)

  • Buckets: 65,536 Batches: 64 Memory Usage: 2,782kB
13. 1,601.885 1,601.885 ↑ 1.0 2,606,963 1

Seq Scan on vw_modelo_reprodutivo v_2 (cost=0.00..185,148.30 rows=2,615,330 width=21) (actual time=0.027..1,601.885 rows=2,606,963 loops=1)

14.          

CTE animais

15. 16.033 14,200.481 ↓ 27.7 13,271 1

Nested Loop (cost=0.43..8,264.04 rows=479 width=140) (actual time=14,129.871..14,200.481 rows=13,271 loops=1)

16. 14,136.577 14,136.577 ↓ 16.0 15,957 1

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

17. 47.871 47.871 ↑ 1.0 1 15,957

Index Scan using mbw_animal_pkey on mbw_animal a_1 (cost=0.43..8.24 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
18. 14,208.035 14,208.035 ↓ 27.7 13,271 1

CTE Scan on animais a (cost=0.00..9.58 rows=479 width=140) (actual time=14,129.874..14,208.035 rows=13,271 loops=1)

19. 6.104 4,928.621 ↓ 258.0 26,320 1

Hash (cost=2.04..2.04 rows=102 width=36) (actual time=4,928.621..4,928.621 rows=26,320 loops=1)

  • Buckets: 32,768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1,468kB
20. 4,922.517 4,922.517 ↓ 258.0 26,320 1

CTE Scan on animais_view v (cost=0.00..2.04 rows=102 width=36) (actual time=4,520.316..4,922.517 rows=26,320 loops=1)

Planning time : 1.027 ms
Execution time : 19,150.085 ms