explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1czz : Optimization for: plan #nFty

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 8.217 20,703.918 ↓ 27.7 13,277 1

Hash Left Join (cost=401,812.09..401,823.48 rows=479 width=140) (actual time=20,617.078..20,703.918 rows=13,277 loops=1)

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

CTE estoque

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

4.          

CTE animais_view

5. 46.217 6,125.652 ↓ 26,080.0 26,080 1

Hash Join (cost=387,601.17..393,537.77 rows=1 width=13) (actual time=5,999.779..6,125.652 rows=26,080 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. 33.534 2,288.006 ↑ 6.0 26,296 1

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

  • Group Key: v_1.femea_id
8. 132.937 2,254.472 ↓ 1.1 193,963 1

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

  • Sort Key: v_1.femea_id
  • Sort Method: external merge Disk: 4,952kB
9. 2,113.795 2,121.535 ↓ 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.470..2,121.535 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.740 7.740 ↓ 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.740..7.740 rows=1,591,040 loops=1)

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

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

12. 66.670 3,782.618 ↓ 15.2 193,707 1

Hash (cost=170,577.11..170,577.11 rows=12,718 width=21) (actual time=3,782.618..3,782.618 rows=193,707 loops=1)

  • Buckets: 65,536 (originally 16384) Batches: 4 (originally 1) Memory Usage: 3,585kB
13. 146.157 3,715.948 ↓ 15.2 193,707 1

Bitmap Heap Scan on vw_modelo_reprodutivo v_2 (cost=131,644.91..170,577.11 rows=12,718 width=21) (actual time=3,571.684..3,715.948 rows=193,707 loops=1)

  • Recheck Cond: ((propriedade_id = 29,097) AND (contrato_id = 102))
  • Heap Blocks: exact=9,090
14. 3,569.791 3,569.791 ↓ 16.0 203,387 1

Bitmap Index Scan on vw_modelo_reprodutivo_data_hora_cobertura_idx_order (cost=0.00..131,641.73 rows=12,718 width=0) (actual time=3,569.791..3,569.791 rows=203,387 loops=1)

  • Index Cond: ((propriedade_id = 29,097) AND (contrato_id = 102))
15.          

CTE animais

16. 16.245 14,544.893 ↓ 27.7 13,271 1

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

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

CTE Scan on estoque e_1 (cost=0.00..20.00 rows=1,000 width=108) (actual time=14,474.035..14,480.777 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.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
19. 14,552.716 14,552.716 ↓ 27.7 13,271 1

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

20. 6.046 6,142.985 ↓ 26,080.0 26,080 1

Hash (cost=0.02..0.02 rows=1 width=36) (actual time=6,142.985..6,142.985 rows=26,080 loops=1)

  • Buckets: 32,768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1,458kB
21. 6,136.939 6,136.939 ↓ 26,080.0 26,080 1

CTE Scan on animais_view v (cost=0.00..0.02 rows=1 width=36) (actual time=5,999.784..6,136.939 rows=26,080 loops=1)

Planning time : 2.744 ms
Execution time : 20,708.875 ms