explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NYYA

Settings
# exclusive inclusive rows x rows loops node
1. 233.204 101,642.867 ↓ 569,585.0 569,585 1

Group (cost=16,724.04..16,724.05 rows=1 width=8) (actual time=101,067.036..101,642.867 rows=569,585 loops=1)

  • Group Key: ml1.id, ml2.id
2. 1,537.148 101,409.663 ↓ 3,313,227.0 3,313,227 1

Sort (cost=16,724.04..16,724.05 rows=1 width=8) (actual time=101,067.032..101,409.663 rows=3,313,227 loops=1)

  • Sort Key: ml1.id, ml2.id
  • Sort Method: quicksort Memory: 253612kB
3. 41,294.617 99,872.515 ↓ 3,313,227.0 3,313,227 1

Nested Loop (cost=7,713.09..16,724.03 rows=1 width=8) (actual time=47.666..99,872.515 rows=3,313,227 loops=1)

  • Join Filter: ((ml2.lot_id IS NULL) OR (ml2.lot_id = q2.lot_id))
  • Rows Removed by Join Filter: 406504144
4. 254.251 2,253.039 ↓ 3,313,227.0 3,313,227 1

Gather (cost=7,712.66..16,650.44 rows=1 width=16) (actual time=47.661..2,253.039 rows=3,313,227 loops=1)

  • Workers Planned: 1
  • Workers Launched: 0
5. 781.428 1,998.788 ↓ 3,313,227.0 3,313,227 1

Nested Loop (cost=6,712.66..15,650.34 rows=1 width=16) (actual time=46.314..1,998.788 rows=3,313,227 loops=1)

  • Join Filter: ((ml1.lot_id IS NULL) OR (ml1.lot_id = q1.lot_id))
  • Rows Removed by Join Filter: 1312618
6. 73.150 592.151 ↓ 1,717.6 48,093 1

Nested Loop (cost=6,712.24..13,589.84 rows=28 width=24) (actual time=46.303..592.151 rows=48,093 loops=1)

  • Join Filter: (((m1.raw_material_production_id IS NOT NULL) AND (m2.production_id IS NOT NULL)) OR ((m1.consume_unbuild_id IS NOT NULL) AND (m2.unbuild_id IS NOT NULL)))
7. 34.941 422.754 ↓ 93.9 96,247 1

Nested Loop (cost=6,711.95..13,249.23 rows=1,025 width=28) (actual time=46.287..422.754 rows=96,247 loops=1)

  • Join Filter: (sqcr.produce_quant_id = sqmr2.quant_id)
8. 46.511 291.627 ↓ 39.0 48,093 1

Nested Loop (cost=6,711.53..12,608.72 rows=1,234 width=32) (actual time=46.277..291.627 rows=48,093 loops=1)

9. 9.686 197.023 ↓ 39.0 48,093 1

Nested Loop (cost=6,711.10..11,960.28 rows=1,234 width=24) (actual time=46.270..197.023 rows=48,093 loops=1)

  • Join Filter: (sqcr.consume_quant_id = q1.id)
10. 17.554 91.151 ↓ 9.5 48,093 1

Merge Join (cost=6,710.68..9,544.28 rows=5,085 width=28) (actual time=46.258..91.151 rows=48,093 loops=1)

  • Merge Cond: (sqmr1.quant_id = sqcr.consume_quant_id)
11. 18.322 47.186 ↓ 1.3 7,186 1

Sort (cost=6,705.19..6,719.23 rows=5,617 width=20) (actual time=46.238..47.186 rows=7,186 loops=1)

  • Sort Key: sqmr1.quant_id
  • Sort Method: quicksort Memory: 4097kB
12. 8.231 28.864 ↓ 9.7 54,621 1

Nested Loop (cost=39.39..6,355.37 rows=5,617 width=20) (actual time=1.660..28.864 rows=54,621 loops=1)

13. 1.673 2.193 ↑ 1.0 1,844 1

Parallel Bitmap Heap Scan on stock_move m1 (cost=38.97..2,378.79 rows=1,923 width=12) (actual time=1.634..2.193 rows=1,844 loops=1)

  • Recheck Cond: ((raw_material_production_id IS NOT NULL) OR (consume_unbuild_id IS NOT NULL))
  • Heap Blocks: exact=990
14. 0.001 0.520 ↓ 0.0 0 1

BitmapOr (cost=38.97..38.97 rows=3,269 width=0) (actual time=0.520..0.520 rows=0 loops=1)

15. 0.516 0.516 ↓ 1.1 3,688 1

Bitmap Index Scan on sm_raw_material_production_id_idx (cost=0.00..35.93 rows=3,269 width=0) (actual time=0.516..0.516 rows=3,688 loops=1)

  • Index Cond: (raw_material_production_id IS NOT NULL)
16. 0.003 0.003 ↓ 0.0 0 1

Bitmap Index Scan on stock_move_consume_unbuild_id_fkey_mig_10_0_idx (cost=0.00..1.40 rows=1 width=0) (actual time=0.003..0.003 rows=0 loops=1)

  • Index Cond: (consume_unbuild_id IS NOT NULL)
17. 18.440 18.440 ↓ 1.2 30 1,844

Index Scan using stock_quant_move_rel_move_id_idx on stock_quant_move_rel sqmr1 (cost=0.42..1.81 rows=26 width=8) (actual time=0.003..0.010 rows=30 loops=1,844)

  • Index Cond: (move_id = m1.id)
18. 26.411 26.411 ↑ 2.0 48,247 1

Index Scan using sqcr_consume_quant_id_idx on stock_quant_consume_rel sqcr (cost=0.43..2,516.55 rows=97,459 width=8) (actual time=0.017..26.411 rows=48,247 loops=1)

  • Index Cond: (consume_quant_id < 38000)
19. 96.186 96.186 ↑ 1.0 1 48,093

Index Scan using stock_quant_pkey on stock_quant q1 (cost=0.42..0.46 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=48,093)

  • Index Cond: (id = sqmr1.quant_id)
20. 48.093 48.093 ↑ 1.0 1 48,093

Index Scan using stock_quant_pkey on stock_quant q2 (cost=0.42..0.53 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=48,093)

  • Index Cond: (id = sqcr.produce_quant_id)
21. 96.186 96.186 ↑ 1.5 2 48,093

Index Scan using sqmr_quant_id_idx on stock_quant_move_rel sqmr2 (cost=0.42..0.48 rows=3 width=8) (actual time=0.002..0.002 rows=2 loops=48,093)

  • Index Cond: (quant_id = q2.id)
22. 96.247 96.247 ↓ 0.0 0 96,247

Index Scan using stock_move_pkey on stock_move m2 (cost=0.29..0.32 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=96,247)

  • Index Cond: (id = sqmr2.move_id)
  • Filter: ((production_id IS NOT NULL) OR (unbuild_id IS NOT NULL))
  • Rows Removed by Filter: 1
23. 625.209 625.209 ↑ 25.4 96 48,093

Index Scan using stock_move_line_move_id_index on stock_move_line ml1 (cost=0.42..43.11 rows=2,438 width=12) (actual time=0.002..0.013 rows=96 loops=48,093)

  • Index Cond: (move_id = sqmr1.move_id)
24. 56,324.859 56,324.859 ↑ 19.7 124 3,313,227

Index Scan using stock_move_line_move_id_index on stock_move_line ml2 (cost=0.42..43.11 rows=2,438 width=12) (actual time=0.002..0.017 rows=124 loops=3,313,227)

  • Index Cond: (move_id = sqmr2.move_id)
Planning time : 19.833 ms
Execution time : 101,655.673 ms