explain.depesz.com

PostgreSQL's explain analyze made readable

Result: G9dq

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 1,643.689 ↓ 1.4 481 1

Group (cost=24,819.33..24,843.71 rows=332 width=8) (actual time=1,611.308..1,643.689 rows=481 loops=1)

  • Group Key: m1.id, m2.id
2. 25.173 1,643.761 ↓ 2.5 481 1

Gather Merge (cost=24,819.33..24,842.73 rows=195 width=8) (actual time=1,611.307..1,643.761 rows=481 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
3. 19.554 1,618.588 ↓ 1.2 240 2 / 2

Group (cost=23,819.32..23,820.79 rows=195 width=8) (actual time=1,588.402..1,618.588 rows=240 loops=2)

  • Group Key: m1.id, m2.id
4. 65.373 1,599.034 ↓ 1,727.6 336,886 2 / 2

Sort (cost=23,819.32..23,819.81 rows=195 width=8) (actual time=1,588.399..1,599.034 rows=336,886 loops=2)

  • Sort Key: m1.id, m2.id
  • Sort Method: quicksort Memory: 29,208kB
5. 150.501 1,533.661 ↓ 1,727.6 336,886 2 / 2

Nested Loop (cost=2,553.26..23,811.91 rows=195 width=8) (actual time=6.136..1,533.661 rows=336,886 loops=2)

6. 99.908 1,046.274 ↓ 429.2 336,886 2 / 2

Hash Join (cost=2,552.84..23,456.71 rows=785 width=16) (actual time=6.124..1,046.274 rows=336,886 loops=2)

  • Hash Cond: (sqmr2.move_id = m2.id)
  • 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. 0.000 941.940 ↓ 23.3 673,804 2 / 2

Nested Loop (cost=41.34..20,869.35 rows=28,902 width=24) (actual time=1.644..941.940 rows=673,804 loops=2)

8. 30.622 269.137 ↓ 38.8 336,886 2 / 2

Nested Loop (cost=40.92..16,452.23 rows=8,672 width=16) (actual time=1.615..269.137 rows=336,886 loops=2)

9. 8.711 74.652 ↓ 4.7 27,310 2 / 2

Nested Loop (cost=40.49..9,116.35 rows=5,761 width=20) (actual time=1.250..74.652 rows=27,310 loops=2)

10. 2.685 11.320 ↓ 4.7 27,310 2 / 2

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

11. 0.968 1.259 ↑ 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=1.008..1.259 rows=922 loops=2)

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

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

13. 0.288 0.288 ↓ 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.577..0.577 rows=3,688 loops=1)

  • Index Cond: (raw_material_production_id IS NOT NULL)
14. 0.002 0.002 ↓ 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.003..0.003 rows=0 loops=1)

  • Index Cond: (consume_unbuild_id IS NOT NULL)
15. 7.376 7.376 ↓ 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.003..0.008 rows=30 loops=1,844)

  • Index Cond: (move_id = m1.id)
16. 54.621 54.621 ↑ 1.0 1 54,621 / 2

Index Only Scan using stock_quant_pkey on stock_quant q1 (cost=0.42..0.46 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=54,621)

  • Index Cond: (id = sqmr1.quant_id)
  • Heap Fetches: 2,288
17. 163.863 163.863 ↑ 1.8 12 54,621 / 2

Index Scan using sqcr_consume_quant_id_idx on stock_quant_consume_rel sqcr (cost=0.43..1.05 rows=22 width=8) (actual time=0.000..0.006 rows=12 loops=54,621)

  • Index Cond: ((consume_quant_id = q1.id) AND (consume_quant_id < 45000))
18. 673.773 673.773 ↑ 1.5 2 673,773 / 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=673,773)

  • Index Cond: (quant_id = sqcr.produce_quant_id)
19. 0.196 4.426 ↑ 1.9 1,847 2 / 2

Hash (cost=2,468.68..2,468.68 rows=3,425 width=12) (actual time=4.426..4.426 rows=1,847 loops=2)

  • Buckets: 4,096 Batches: 1 Memory Usage: 105kB
20. 3.502 4.230 ↑ 1.9 1,847 2 / 2

Bitmap Heap Scan on stock_move m2 (cost=41.19..2,468.68 rows=3,425 width=12) (actual time=3.344..4.230 rows=1,847 loops=2)

  • Recheck Cond: ((production_id IS NOT NULL) OR (unbuild_id IS NOT NULL))
  • Heap Blocks: exact=1,016
21. 0.001 0.728 ↓ 0.0 0 2 / 2

BitmapOr (cost=41.19..41.19 rows=3,425 width=0) (actual time=0.728..0.728 rows=0 loops=2)

22. 0.661 0.661 ↓ 1.1 3,696 2 / 2

Bitmap Index Scan on stock_move_production_id_fkey_mig_10_0_idx (cost=0.00..38.08 rows=3,425 width=0) (actual time=0.661..0.661 rows=3,696 loops=2)

  • Index Cond: (production_id IS NOT NULL)
23. 0.066 0.066 ↓ 0.0 0 2 / 2

Bitmap Index Scan on stock_move_unbuild_id_fkey_mig_10_0_idx (cost=0.00..1.40 rows=1 width=0) (actual time=0.066..0.066 rows=0 loops=2)

  • Index Cond: (unbuild_id IS NOT NULL)
24. 336.887 336.887 ↑ 1.0 1 673,773 / 2

Index Only Scan using stock_quant_pkey on stock_quant q2 (cost=0.42..0.45 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=673,773)

  • Index Cond: (id = sqcr.produce_quant_id)
  • Heap Fetches: 360,951
Planning time : 10.644 ms
Execution time : 1,645.383 ms