explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DGoC

Settings
# exclusive inclusive rows x rows loops node
1. 54.254 16,231.991 ↓ 24,351.0 24,351 1

Group (cost=18,263.06..18,263.07 rows=1 width=8) (actual time=16,107.372..16,231.991 rows=24,351 loops=1)

  • Group Key: sml1.id, sml2.id
2. 329.816 16,177.737 ↓ 945,059.0 945,059 1

Sort (cost=18,263.06..18,263.07 rows=1 width=8) (actual time=16,107.369..16,177.737 rows=945,059 loops=1)

  • Sort Key: sml1.id, sml2.id
  • Sort Method: quicksort Memory: 68876kB
3. 5,968.726 15,847.921 ↓ 945,059.0 945,059 1

Nested Loop (cost=4,706.47..18,263.05 rows=1 width=8) (actual time=1,277.243..15,847.921 rows=945,059 loops=1)

  • Join Filter: (NOT (sml2.lot_id IS DISTINCT FROM sq2.lot_id))
  • Rows Removed by Join Filter: 56300716
4. 0.000 1,373.664 ↓ 945,059.0 945,059 1

Gather (cost=4,706.05..18,217.00 rows=1 width=16) (actual time=1,277.220..1,373.664 rows=945,059 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 158.500 1,426.217 ↓ 315,020.0 315,020 3 / 3

Parallel Hash Join (cost=3,706.05..17,216.90 rows=1 width=16) (actual time=1,271.515..1,426.217 rows=315,020 loops=3)

  • Hash Cond: (sml1.move_id = sqmr1.move_id)
  • Join Filter: (NOT (sml1.lot_id IS DISTINCT FROM sq1.lot_id))
  • Rows Removed by Join Filter: 389453
6. 20.692 20.692 ↑ 1.2 101,330 3 / 3

Parallel Seq Scan on stock_move_line sml1 (cost=0.00..8,471.62 rows=126,662 width=12) (actual time=3.371..20.692 rows=101,330 loops=3)

7. 646.798 1,247.025 ↓ 6.8 8,117 3 / 3

Parallel Hash (cost=3,691.11..3,691.11 rows=1,195 width=24) (actual time=1,247.025..1,247.025 rows=8,117 loops=3)

  • Buckets: 32768 (originally 4096) Batches: 1 (originally 1) Memory Usage: 1824kB
8. 5.027 600.227 ↓ 6.8 8,117 3 / 3

Nested Loop (cost=3.19..3,691.11 rows=1,195 width=24) (actual time=2.268..600.227 rows=8,117 loops=3)

  • Join Filter: (sqmr2.quant_id = sq2.id)
9. 1.349 587.083 ↓ 3.3 8,117 3 / 3

Merge Join (cost=2.77..2,594.24 rows=2,433 width=28) (actual time=2.260..587.083 rows=8,117 loops=3)

  • Merge Cond: (sm1.raw_material_production_id = mp.id)
10. 5.239 585.491 ↓ 811.8 8,118 3 / 3

Nested Loop (cost=2.49..25,700.37 rows=10 width=36) (actual time=2.060..585.491 rows=8,118 loops=3)

  • Join Filter: (sqmr1.quant_id = sq1.id)
11. 79.243 572.134 ↓ 405.9 8,118 3 / 3

Nested Loop (cost=2.07..25,691.36 rows=20 width=40) (actual time=2.002..572.134 rows=8,118 loops=3)

12. 34.390 86.982 ↓ 17.2 405,909 3 / 3

Nested Loop (cost=1.63..9,854.02 rows=23,641 width=32) (actual time=1.851..86.982 rows=405,909 loops=3)

13. 0.350 3.655 ↑ 1.2 2,879 3 / 3

Nested Loop (cost=1.21..5,611.48 rows=3,558 width=24) (actual time=1.843..3.655 rows=2,879 loops=3)

14. 0.292 2.498 ↑ 3.3 161 3 / 3

Merge Join (cost=0.79..4,973.55 rows=535 width=16) (actual time=1.794..2.498 rows=161 loops=3)

  • Merge Cond: (sm1.raw_material_production_id = sm2.production_id)
15. 0.203 0.203 ↑ 211.8 109 3 / 3

Parallel Index Scan using stock_move_raw_material_production_id_fkey_mig_10_0_idx on stock_move sm1 (cost=0.29..4,775.45 rows=23,085 width=8) (actual time=0.053..0.203 rows=109 loops=3)

16. 2.003 2.003 ↑ 120.2 461 3 / 3

Index Scan using stock_move_production_id_fkey_mig_10_0_idx on stock_move sm2 (cost=0.29..5,104.38 rows=55,405 width=8) (actual time=0.040..2.003 rows=461 loops=3)

17. 0.807 0.807 ↑ 1.4 18 484 / 3

Index Scan using stock_quant_move_rel_move_id_idx on stock_quant_move_rel sqmr1 (cost=0.42..0.93 rows=26 width=8) (actual time=0.003..0.005 rows=18 loops=484)

  • Index Cond: (move_id = sm1.id)
18. 48.937 48.937 ↓ 5.4 141 8,636 / 3

Index Scan using stock_quant_move_rel_move_id_idx on stock_quant_move_rel sqmr2 (cost=0.42..0.93 rows=26 width=8) (actual time=0.002..0.017 rows=141 loops=8,636)

  • Index Cond: (move_id = sm2.id)
19. 405.909 405.909 ↓ 0.0 0 1,217,727 / 3

Index Only Scan using stock_quant_consume_rel_consume_quant_id_produce_quant_id_key on stock_quant_consume_rel sqcr (cost=0.43..0.67 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=1,217,727)

  • Index Cond: ((consume_quant_id = sqmr1.quant_id) AND (produce_quant_id = sqmr2.quant_id))
  • Heap Fetches: 24354
20. 8.118 8.118 ↑ 1.0 1 24,354 / 3

Index Scan using stock_quant_pkey on stock_quant sq1 (cost=0.42..0.44 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=24,354)

  • Index Cond: (id = sqcr.consume_quant_id)
21. 0.243 0.243 ↓ 1.0 283 3 / 3

Index Only Scan using mrp_production_pkey on mrp_production mp (cost=0.28..21.25 rows=282 width=4) (actual time=0.054..0.243 rows=283 loops=3)

  • Index Cond: (id < 300)
  • Heap Fetches: 849
22. 8.117 8.117 ↑ 1.0 1 24,351 / 3

Index Scan using stock_quant_pkey on stock_quant sq2 (cost=0.42..0.44 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=24,351)

  • Index Cond: (id = sqcr.produce_quant_id)
23. 8,505.531 8,505.531 ↑ 24.9 61 945,059

Index Scan using stock_move_line_move_id_index on stock_move_line sml2 (cost=0.42..27.05 rows=1,520 width=12) (actual time=0.002..0.009 rows=61 loops=945,059)

  • Index Cond: (move_id = sqmr2.move_id)
Planning time : 20.601 ms
Execution time : 16,236.939 ms