explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oQ5

Settings
# exclusive inclusive rows x rows loops node
1. 0.052 5,069,528.850 ↑ 1.0 100 1

Limit (cost=227,449,689.41..227,449,689.91 rows=100 width=14) (actual time=5,069,528.712..5,069,528.850 rows=100 loops=1)

2. 0.047 5,069,528.798 ↑ 3.4 100 1

Unique (cost=227,449,689.41..227,449,691.11 rows=340 width=14) (actual time=5,069,528.706..5,069,528.798 rows=100 loops=1)

3. 151.375 5,069,528.751 ↑ 3.4 100 1

Sort (cost=227,449,689.41..227,449,690.26 rows=340 width=14) (actual time=5,069,528.704..5,069,528.751 rows=100 loops=1)

  • Sort Key: skulskul2.item_no NULLS FIRST
  • Sort Method: quicksort Memory: 16074kB
4. 24,388.576 5,069,377.376 ↓ 621.5 211,308 1

Unique (cost=227,449,673.07..227,449,674.77 rows=340 width=14) (actual time=4,979,063.398..5,069,377.376 rows=211,308 loops=1)

5. 580,436.586 5,044,988.800 ↓ 349,094.7 118,692,191 1

Sort (cost=227,449,673.07..227,449,673.92 rows=340 width=14) (actual time=4,979,063.395..5,044,988.800 rows=118,692,191 loops=1)

  • Sort Key: skulskul2.item_no
  • Sort Method: external merge Disk: 2816752kB
6. 143,372.690 4,464,552.214 ↓ 349,094.7 118,692,191 1

Hash Join (cost=202,940,298.40..227,449,658.78 rows=340 width=14) (actual time=3,481,252.646..4,464,552.214 rows=118,692,191 loops=1)

  • Hash Cond: (((demand_trans_supercession.item_no)::text = (skulskul2.item_no)::text) AND (demand_trans.skul_no = skulskul2.skul_no))
7. 278,868.940 4,315,859.222 ↓ 1.7 197,836,100 1

Hash Join (cost=201,595,785.83..220,151,779.55 rows=118,414,791 width=154) (actual time=3,475,364.300..4,315,859.222 rows=197,836,100 loops=1)

  • Hash Cond: (demand_trans_supercession.transaction_id = demand_trans.transaction_id)
8. 64,995.301 2,720,001.332 ↓ 1.7 197,836,100 1

Subquery Scan on demand_trans_supercession (cost=191,435,469.87..203,395,363.77 rows=118,414,791 width=156) (actual time=2,150,201.980..2,720,001.332 rows=197,836,100 loops=1)

9. 320,699.572 2,655,006.031 ↓ 1.7 197,836,100 1

Unique (cost=191,435,469.87..203,276,948.97 rows=118,414,791 width=2,266) (actual time=2,150,201.978..2,655,006.031 rows=197,836,100 loops=1)

10. 1,284,140.367 2,334,306.459 ↓ 1.7 197,836,100 1

Sort (cost=191,435,469.87..191,731,506.85 rows=118,414,791 width=2,266) (actual time=2,150,201.975..2,334,306.459 rows=197,836,100 loops=1)

  • Sort Key: dt.transaction_id, (CASE WHEN ((s.on_hand_roll_up = '1'::numeric) OR (dt.status <> '00'::bpchar)) THEN s.superceding_item_no ELSE dt.item_no END), (CASE WHEN ((s.on_hand_roll_up = '1'::numeric) OR (dt.status <> '00'::bpchar)) THEN s.superceding_loc_no ELSE dt.ideal_ship_loc END), dt.actual_ship_loc, dt.item_desc, dt.request_date, dt.order_entry_date, dt.promise_date, dt.release_date, dt.order_ship_date, dt.request_qty, dt.ship_qty, dt.sale_price, dt.po_number, dt.line_number, dt.customer_ship_to_code, dt.customer_code, dt.customer_desc, dt.order_fulfil_cost, dt.order_revenue, dt.changed, dt.adjusted, dt.channel, dt.status, dt.split, dt.bto_kit_item, dt.bto_kit_loc, dt.exclude_flag, dt.actual_item_no, dt.outlier_flag, dt.outlier_flag_override, (1), dt.demand_type, dt.lot_no, (CASE WHEN ((s.on_hand_roll_up = '1'::numeric) OR (dt.status <> '00'::bpchar)) THEN s.superceding_skul_no ELSE dt.skul_no END), dt.line_comment, demand_trans_override.new_ideal_ship_loc, demand_trans_override.comments, demand_trans_override.reason_code
  • Sort Method: external merge Disk: 58451016kB
11. 67,067.027 1,050,166.092 ↓ 1.7 197,836,100 1

Append (cost=5,661.54..19,570,427.51 rows=118,414,791 width=2,266) (actual time=140.967..1,050,166.092 rows=197,836,100 loops=1)

12. 2,106.640 385,240.817 ↑ 21.8 1,822,413 1

Hash Left Join (cost=5,661.54..9,994,510.47 rows=39,695,045 width=1,240) (actual time=140.966..385,240.817 rows=1,822,413 loops=1)

  • Hash Cond: ((dt.transaction_id)::numeric = demand_trans_override.transaction_id)
13. 52,252.875 383,134.161 ↑ 21.8 1,822,413 1

Hash Join (cost=5,650.96..9,289,267.79 rows=39,695,045 width=525) (actual time=140.905..383,134.161 rows=1,822,413 loops=1)

  • Hash Cond: (dt.skul_no = s.superceded_skul_no)
14. 330,740.816 330,740.816 ↓ 1.0 197,836,100 1

Seq Scan on demand_trans dt (cost=0.00..8,502,153.88 rows=197,804,880 width=499) (actual time=0.015..330,740.816 rows=197,836,100 loops=1)

15. 56.604 140.470 ↓ 3.0 102,877 1

Hash (cost=5,530.73..5,530.73 rows=34,350 width=32) (actual time=140.470..140.470 rows=102,877 loops=1)

  • Buckets: 131072 (originally 65536) Batches: 1 (originally 1) Memory Usage: 7667kB
16. 57.787 83.866 ↓ 3.0 102,877 1

Nested Loop (cost=0.00..5,530.73 rows=34,350 width=32) (actual time=0.032..83.866 rows=102,877 loops=1)

  • Join Filter: ((s.effective_date IS NULL) OR (s.effective_date <= c.w_plan_period))
  • Rows Removed by Join Filter: 174
17. 0.016 0.016 ↑ 1.0 1 1

Seq Scan on control c (cost=0.00..1.00 rows=1 width=8) (actual time=0.015..0.016 rows=1 loops=1)

18. 26.063 26.063 ↑ 1.0 103,051 1

Seq Scan on supercession s (cost=0.00..5,169.05 rows=103,051 width=36) (actual time=0.011..26.063 rows=103,051 loops=1)

19. 0.001 0.016 ↓ 0.0 0 1

Hash (cost=10.13..10.13 rows=130 width=582) (actual time=0.016..0.016 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
20. 0.015 0.015 ↓ 0.0 0 1

Seq Scan on demand_trans_override (cost=0.00..10.13 rows=130 width=582) (actual time=0.015..0.015 rows=0 loops=1)

21. 149,446.675 597,858.248 ↓ 2.5 196,013,687 1

Hash Left Join (cost=5,661.54..9,457,502.25 rows=78,719,746 width=1,065) (actual time=132.942..597,858.248 rows=196,013,687 loops=1)

  • Hash Cond: ((dt_1.transaction_id)::numeric = demand_trans_override_1.transaction_id)
22. 138,578.290 448,411.548 ↓ 2.5 196,013,687 1

Hash Anti Join (cost=5,650.96..9,239,733.17 rows=78,719,746 width=499) (actual time=132.895..448,411.548 rows=196,013,687 loops=1)

  • Hash Cond: (dt_1.skul_no = s_1.superceded_skul_no)
23. 309,700.558 309,700.558 ↓ 1.0 197,836,100 1

Seq Scan on demand_trans dt_1 (cost=0.00..8,502,153.88 rows=197,804,880 width=499) (actual time=0.023..309,700.558 rows=197,836,100 loops=1)

24. 47.638 132.700 ↓ 3.0 102,877 1

Hash (cost=5,530.73..5,530.73 rows=34,350 width=6) (actual time=132.700..132.700 rows=102,877 loops=1)

  • Buckets: 131072 (originally 65536) Batches: 1 (originally 1) Memory Usage: 4940kB
25. 57.408 85.062 ↓ 3.0 102,877 1

Nested Loop (cost=0.00..5,530.73 rows=34,350 width=6) (actual time=0.031..85.062 rows=102,877 loops=1)

  • Join Filter: ((s_1.effective_date IS NULL) OR (s_1.effective_date <= c_1.w_plan_period))
  • Rows Removed by Join Filter: 174
26. 0.016 0.016 ↑ 1.0 1 1

Seq Scan on control c_1 (cost=0.00..1.00 rows=1 width=8) (actual time=0.016..0.016 rows=1 loops=1)

27. 27.638 27.638 ↑ 1.0 103,051 1

Seq Scan on supercession s_1 (cost=0.00..5,169.05 rows=103,051 width=10) (actual time=0.009..27.638 rows=103,051 loops=1)

28. 0.001 0.025 ↓ 0.0 0 1

Hash (cost=10.13..10.13 rows=130 width=582) (actual time=0.025..0.025 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
29. 0.024 0.024 ↓ 0.0 0 1

Seq Scan on demand_trans_override demand_trans_override_1 (cost=0.00..10.13 rows=130 width=582) (actual time=0.024..0.024 rows=0 loops=1)

30. 92,535.715 1,316,988.950 ↓ 1.0 197,836,100 1

Hash (cost=8,502,153.88..8,502,153.88 rows=197,804,880 width=14) (actual time=1,316,988.950..1,316,988.950 rows=197,836,100 loops=1)

  • Buckets: 2097152 Batches: 128 Memory Usage: 83164kB
31. 1,224,453.235 1,224,453.235 ↓ 1.0 197,836,100 1

Seq Scan on demand_trans (cost=0.00..8,502,153.88 rows=197,804,880 width=14) (actual time=0.062..1,224,453.235 rows=197,836,100 loops=1)

32. 1,319.677 5,320.302 ↓ 1.0 2,098,468 1

Hash (cost=1,319,672.51..1,319,672.51 rows=2,094,510 width=20) (actual time=5,320.302..5,320.302 rows=2,098,468 loops=1)

  • Buckets: 2097152 Batches: 2 Memory Usage: 71240kB
33. 4,000.625 4,000.625 ↓ 1.0 2,098,468 1

Seq Scan on skulskul2 (cost=0.00..1,319,672.51 rows=2,094,510 width=20) (actual time=0.025..4,000.625 rows=2,098,468 loops=1)