explain.depesz.com

PostgreSQL's explain analyze made readable

Result: irAb

Settings
# exclusive inclusive rows x rows loops node
1. 1.771 9,624.370 ↓ 6.4 16,141 1

Hash Join (cost=184,695.84..413,161.67 rows=2,519 width=4) (actual time=8,299.723..9,624.370 rows=16,141 loops=1)

  • Hash Cond: (pn.id = gp.plano_nutricional_id)
2. 1.338 9,622.309 ↓ 39.4 15,589 1

Merge Join (cost=184,633.76..413,069.92 rows=396 width=8) (actual time=8,299.430..9,622.309 rows=15,589 loops=1)

  • Merge Cond: (a.lote_alimentar_id = lo.id)
3. 810.993 9,620.871 ↓ 75.7 15,589 1

Nested Loop (cost=184,633.41..1,185,628.98 rows=206 width=16) (actual time=8,299.414..9,620.871 rows=15,589 loops=1)

  • Join Filter: (a.lote_alimentar_id = pn.lote_alimentar_id)
  • Rows Removed by Join Filter: 17502401
4. 0.070 0.070 ↑ 1.0 238 1

Index Scan using mbw_planonutricional_39b2f934 on mbw_planonutricional pn (cost=0.14..21.11 rows=238 width=8) (actual time=0.003..0.070 rows=238 loops=1)

5. 538.312 8,809.808 ↓ 62.1 73,605 238

Materialize (cost=184,633.26..1,181,376.82 rows=1,186 width=8) (actual time=32.712..37.016 rows=73,605 loops=238)

6. 0.000 8,271.496 ↓ 62.1 73,605 1

Hash Join (cost=184,633.26..1,181,370.89 rows=1,186 width=8) (actual time=7,785.413..8,271.496 rows=73,605 loops=1)

  • Hash Cond: ((pm.animal_id = a.id) AND (pm.data_avaliacao = (SubPlan 2)))
7. 7.118 617.574 ↑ 23.8 253,791 1

Nested Loop (cost=13.31..29,034.88 rows=6,052,206 width=12) (actual time=428.448..617.574 rows=253,791 loops=1)

8. 11.545 435.161 ↓ 175.3 35,059 1

HashAggregate (cost=12.75..14.75 rows=200 width=4) (actual time=428.432..435.161 rows=35,059 loops=1)

  • Group Key: obtenha_animais_estoque_por_contrato.animal_id
9. 423.616 423.616 ↓ 35.1 35,059 1

Function Scan on obtenha_animais_estoque_por_contrato (cost=0.25..10.25 rows=1,000 width=4) (actual time=422.325..423.616 rows=35,059 loops=1)

10. 175.295 175.295 ↑ 8.3 7 35,059

Index Only Scan using mbw_pesagem_medicao_animal_data_valor_idx on mbw_pesagem_medicao pm (cost=0.56..144.52 rows=58 width=8) (actual time=0.002..0.005 rows=7 loops=35,059)

  • Index Cond: (animal_id = obtenha_animais_estoque_por_contrato.animal_id)
  • Heap Fetches: 253791
11. 6,524.738 7,346.166 ↑ 1.3 2,264,977 1

Hash (cost=140,195.98..140,195.98 rows=2,961,598 width=8) (actual time=7,346.166..7,346.166 rows=2,264,977 loops=1)

  • Buckets: 4194304 Batches: 1 Memory Usage: 115043kB
12. 821.428 821.428 ↑ 1.0 2,960,864 1

Seq Scan on mbw_animal a (cost=0.00..140,195.98 rows=2,961,598 width=8) (actual time=0.011..821.428 rows=2,960,864 loops=1)

13.          

SubPlan (for Hash Join)

14. 0.000 6,068.938 ↑ 1.0 1 3,034,469

Result (cost=3.08..3.09 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=3,034,469)

15.          

Initplan (for Result)

16. 3,034.469 6,068.938 ↑ 1.0 1 3,034,469

Limit (cost=0.56..3.08 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=3,034,469)

17. 3,034.469 3,034.469 ↑ 58.0 1 3,034,469

Index Only Scan Backward using mbw_pesagem_medicao_animal_data_valor_idx on mbw_pesagem_medicao pm1 (cost=0.56..146.75 rows=58 width=4) (actual time=0.001..0.001 rows=1 loops=3,034,469)

  • Index Cond: ((animal_id = a.id) AND (data_avaliacao IS NOT NULL) AND (data_avaliacao <= CURRENT_DATE))
  • Heap Fetches: 2338582
18. 0.100 0.100 ↑ 1.1 621 1

Index Only Scan using mbw_lotealimentar_pkey on mbw_lotealimentar lo (cost=0.28..51.66 rows=713 width=4) (actual time=0.011..0.100 rows=621 loops=1)

  • Heap Fetches: 621
19. 0.114 0.290 ↑ 1.0 1,515 1

Hash (cost=43.15..43.15 rows=1,515 width=4) (actual time=0.290..0.290 rows=1,515 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 70kB
20. 0.176 0.176 ↑ 1.0 1,515 1

Seq Scan on mbw_ganhopesoengordapasto gp (cost=0.00..43.15 rows=1,515 width=4) (actual time=0.005..0.176 rows=1,515 loops=1)

Planning time : 4.515 ms
Execution time : 9,628.558 ms