explain.depesz.com

PostgreSQL's explain analyze made readable

Result: b6Jd

Settings
# exclusive inclusive rows x rows loops node
1. 407.677 15,189.325 ↓ 20.4 566,319 1

HashAggregate (cost=88,088.44..88,366.40 rows=27,796 width=8) (actual time=15,099.284..15,189.325 rows=566,319 loops=1)

  • Group Key: ml1.id, ml2.id
2.          

CTE a

3. 65.475 246.227 ↓ 569.1 26,749 1

Gather (cost=7,818.16..14,466.73 rows=47 width=16) (actual time=45.595..246.227 rows=26,749 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
4. 18.504 180.752 ↓ 477.6 13,374 2 / 2

Nested Loop (cost=6,818.16..13,462.03 rows=28 width=16) (actual time=39.900..180.752 rows=13,374 loops=2)

  • 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)))
5. 4.056 135.469 ↓ 26.5 26,780 2 / 2

Nested Loop (cost=6,817.87..13,126.40 rows=1,010 width=24) (actual time=39.872..135.469 rows=26,780 loops=2)

  • Join Filter: (sqcr.produce_quant_id = sqmr2.quant_id)
6. 9.934 104.664 ↓ 11.0 13,374 2 / 2

Nested Loop (cost=6,817.44..12,495.23 rows=1,216 width=28) (actual time=39.848..104.664 rows=13,374 loops=2)

7. 10.284 81.355 ↓ 11.0 13,374 2 / 2

Nested Loop (cost=6,817.02..11,852.03 rows=1,216 width=20) (actual time=39.833..81.355 rows=13,374 loops=2)

  • Join Filter: (sqcr.consume_quant_id = q1.id)
8. 3.780 57.697 ↓ 2.7 13,374 2 / 2

Merge Join (cost=6,816.60..9,531.53 rows=4,884 width=24) (actual time=39.803..57.697 rows=13,374 loops=2)

  • Merge Cond: (sqmr1.quant_id = sqcr.consume_quant_id)
9. 5.684 40.067 ↑ 1.7 3,374 2 / 2

Sort (cost=6,811.02..6,825.43 rows=5,761 width=16) (actual time=39.768..40.067 rows=3,374 loops=2)

  • Sort Key: sqmr1.quant_id
  • Sort Method: quicksort Memory: 2148kB
10. 2.466 34.383 ↓ 4.7 27,310 2 / 2

Nested Loop (cost=40.06..6,451.19 rows=5,761 width=16) (actual time=21.089..34.383 rows=27,310 loops=2)

11. 21.547 21.775 ↑ 2.1 922 2 / 2

Parallel Bitmap Heap Scan on stock_move m1 (cost=39.64..2,419.24 rows=1,972 width=12) (actual time=21.024..21.775 rows=922 loops=2)

  • Recheck Cond: ((raw_material_production_id IS NOT NULL) OR (consume_unbuild_id IS NOT NULL))
  • Heap Blocks: exact=674
12. 0.001 0.228 ↓ 0.0 0 1 / 2

BitmapOr (cost=39.64..39.64 rows=3,353 width=0) (actual time=0.455..0.455 rows=0 loops=1)

13. 0.220 0.220 ↓ 1.1 3,688 1 / 2

Bitmap Index Scan on sm_raw_material_production_id_idx (cost=0.00..36.56 rows=3,353 width=0) (actual time=0.440..0.440 rows=3,688 loops=1)

  • Index Cond: (raw_material_production_id IS NOT NULL)
14. 0.007 0.007 ↓ 0.0 0 1 / 2

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.014..0.014 rows=0 loops=1)

  • Index Cond: (consume_unbuild_id IS NOT NULL)
15. 10.142 10.142 ↓ 1.2 30 1,844 / 2

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

  • Index Cond: (move_id = m1.id)
16. 13.850 13.850 ↑ 3.5 26,903 2 / 2

Index Scan using sqcr_consume_quant_id_idx on stock_quant_consume_rel sqcr (cost=0.43..2,408.73 rows=93,656 width=8) (actual time=0.029..13.850 rows=26,903 loops=2)

  • Index Cond: (consume_quant_id < 37000)
17. 13.375 13.375 ↑ 1.0 1 26,749 / 2

Index Scan using stock_quant_pkey on stock_quant q1 (cost=0.42..0.46 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=26,749)

  • Index Cond: (id = sqmr1.quant_id)
18. 13.375 13.375 ↑ 1.0 1 26,749 / 2

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=26,749)

  • Index Cond: (id = sqcr.produce_quant_id)
19. 26.749 26.749 ↑ 1.5 2 26,749 / 2

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.001..0.002 rows=2 loops=26,749)

  • Index Cond: (quant_id = q2.id)
20. 26.780 26.780 ↓ 0.0 0 53,559 / 2

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=53,559)

  • Index Cond: (id = sqmr2.move_id)
  • Filter: ((production_id IS NOT NULL) OR (unbuild_id IS NOT NULL))
  • Rows Removed by Filter: 1
21. 13,612.223 14,781.648 ↓ 53.6 1,489,741 1

Hash Join (cost=10,722.29..73,482.73 rows=27,796 width=8) (actual time=1,183.324..14,781.648 rows=1,489,741 loops=1)

  • Hash Cond: (ml2.move_id = a.m2__id)
  • Join Filter: ((ml2.lot_id IS NULL) OR (ml2.lot_id = a.q2__lot_id))
  • Rows Removed by Join Filter: 216413876
22. 60.419 60.419 ↑ 1.6 303,990 1

Seq Scan on stock_move_line ml2 (cost=0.00..12,081.87 rows=487,587 width=12) (actual time=0.911..60.419 rows=303,990 loops=1)

23. 177.103 1,109.006 ↓ 1,303.4 1,489,741 1

Hash (cost=10,708.00..10,708.00 rows=1,143 width=12) (actual time=1,109.006..1,109.006 rows=1,489,741 loops=1)

  • Buckets: 2097152 (originally 2048) Batches: 1 (originally 1) Memory Usage: 80397kB
24. 90.712 931.903 ↓ 1,303.4 1,489,741 1

Nested Loop (cost=0.42..10,708.00 rows=1,143 width=12) (actual time=45.849..931.903 rows=1,489,741 loops=1)

25. 252.713 252.713 ↓ 569.1 26,749 1

CTE Scan on a (cost=0.00..0.94 rows=47 width=16) (actual time=45.596..252.713 rows=26,749 loops=1)

26. 588.478 588.478 ↓ 2.3 56 26,749

Index Scan using stock_move_line_move_id_index on stock_move_line ml1 (cost=0.42..227.57 rows=24 width=12) (actual time=0.007..0.022 rows=56 loops=26,749)

  • Index Cond: (move_id = a.m1__id)
  • Filter: ((lot_id IS NULL) OR (lot_id = a.q1__lot_id))
  • Rows Removed by Filter: 47
Planning time : 8.733 ms
Execution time : 15,209.533 ms