explain.depesz.com

A tool for finding a real cause for slow queries.

Result: MwR

options
Did it help? Consider supporting us - Bitcoin address: 12v2hUztAk2LgzQ9H9LMwuU32urHMjZQnq
# exclusive inclusive rows x rows loops node
1. 2.910 187788.001 ↓ 153.0 153 1

HashAggregate (cost=9388.82..9388.88 rows=1 width=311) (actual time=187787.183..187788.001 rows=153 loops=1)

2. 11384.374 187785.091 ↓ 153.0 153 1

Nested Loop (cost=9266.68..9388.77 rows=1 width=311) (actual time=8432.315..187785.091 rows=153 loops=1)

  • Join Filter: ((oh.ord_id = ord_line.ord_id) AND (iis.item_id = ord_line.item_id))
3. 16559.051 147161.052 ↓ 5847933.0 5847933 1

Nested Loop Left Join (cost=9266.68..9373.81 rows=1 width=333) (actual time=92.238..147161.052 rows=5847933 loops=1)

  • Join Filter: ((oh.division)::text = (item_color_cd.division)::text)
4. 17581.426 113058.202 ↓ 5847933.0 5847933 1

Nested Loop Left Join (cost=9266.68..9373.53 rows=1 width=322) (actual time=92.214..113058.202 rows=5847933 loops=1)

  • Join Filter: ((oh.division)::text = (item_size_cd.division)::text)
5. 10901.963 77932.977 ↓ 5847933.0 5847933 1

Nested Loop Left Join (cost=9266.68..9373.24 rows=1 width=312) (actual time=92.189..77932.977 rows=5847933 loops=1)

  • Join Filter: ((oh.division)::text = (item_class_cd.division)::text)
6. 7824.914 61183.081 ↓ 5847933.0 5847933 1

Nested Loop (cost=9266.68..9372.92 rows=1 width=294) (actual time=92.183..61183.081 rows=5847933 loops=1)

7. 16.859 58.193 ↓ 8463.0 8463 1

Nested Loop (cost=67.45..91.07 rows=1 width=75) (actual time=0.819..58.193 rows=8463 loops=1)

8. 0.532 3.894 ↓ 360.0 360 1

Nested Loop (cost=67.45..75.24 rows=1 width=51) (actual time=0.784..3.894 rows=360 loops=1)

9. 0.047 0.047 ↑ 1.0 1 1

Index Scan using item_dept_cd_division_key on item_dept_cd (cost=0.00..5.27 rows=1 width=27) (actual time=0.045..0.047 rows=1 loops=1)

  • Index Cond: (((division)::text = 'BSM'::text) AND ((dept_cd)::text = 'S'::text))
  • Filter: ((dept_cd)::text ~~ 'S'::text)
10. 2.628 3.315 ↓ 360.0 360 1

Bitmap Heap Scan on item_item_num iin (cost=67.45..69.96 rows=1 width=32) (actual time=0.734..3.315 rows=360 loops=1)

  • Recheck Cond: ((iin.dept_cd_id = item_dept_cd.dept_cd_id) AND ((iin.division)::text = 'BSM'::text))
11. 0.034 0.687 ↓ 0.0 0 1

BitmapAnd (cost=67.45..67.45 rows=1 width=0) (actual time=0.687..0.687 rows=0 loops=1)

12. 0.109 0.109 ↓ 1.7 364 1

Bitmap Index Scan on item_item_num_dept_id (cost=0.00..6.92 rows=213 width=0) (actual time=0.109..0.109 rows=364 loops=1)

  • Index Cond: (iin.dept_cd_id = item_dept_cd.dept_cd_id)
13. 0.544 0.544 ↓ 1.1 2753 1

Bitmap Index Scan on item_item_num_division_key (cost=0.00..59.64 rows=2573 width=0) (actual time=0.544..0.544 rows=2753 loops=1)

  • Index Cond: ((iin.division)::text = 'BSM'::text)
14. 37.440 37.440 ↓ 24.0 24 360

Index Scan using item_sku_item_num_id_key on item_sku iis (cost=0.00..15.82 rows=1 width=32) (actual time=0.021..0.104 rows=24 loops=360)

  • Index Cond: (iis.item_num_id = iin.item_num_id)
  • Filter: ((iis.division)::text = 'BSM'::text)
15. 18122.892 53299.974 ↓ 86.4 691 8463

HashAggregate (cost=9199.23..9281.69 rows=8 width=66) (actual time=0.020..6.298 rows=691 loops=8463)

16. 2.705 89.484 ↓ 86.4 691 1

Nested Loop (cost=0.01..9198.91 rows=8 width=66) (actual time=1.111..89.484 rows=691 loops=1)

17. 79.178 79.178 ↑ 1.0 691 1

Index Scan using ledger_demand_ix4 on ledger_demand ls (cost=0.01..4512.71 rows=710 width=35) (actual time=1.078..79.178 rows=691 loops=1)

  • Index Cond: ((date(trans_date) >= '2012-01-01'::date) AND (date(trans_date) <= '2012-01-04'::date))
  • Filter: (((division)::text = 'BSM'::text) AND (((qty_ordered > 0) AND ((ledger_code)::text ~~ 'D%'::text)) OR ((ledger_code)::text ~~ 'DC%'::text)))
18. 7.601 7.601 ↑ 1.0 1 691

Index Scan using ord_header_pkey on ord_header oh (cost=0.00..6.59 rows=1 width=39) (actual time=0.010..0.011 rows=1 loops=691)

  • Index Cond: (oh.ord_id = ls.ord_id)
  • Filter: ((oh.division)::text = 'BSM'::text)
19.          

SubPlan (forHashAggregate)

20. 11695.866 35087.598 ↑ 1.0 1 5847933

Aggregate (cost=10.25..10.26 rows=1 width=8) (actual time=0.005..0.006 rows=1 loops=5847933)

21. 23391.732 23391.732 ↓ 0.0 0 5847933

Index Scan using ledger_discount_ord_line_id on ledger_discount (cost=0.00..10.25 rows=1 width=8) (actual time=0.003..0.004 rows=0 loops=5847933)

  • Index Cond: (ord_line_id = $2)
  • Filter: (((ledger_code)::text ~~ 'D%'::text) AND ((division)::text = ($0)::text) AND (ord_id = $1) AND (date(trans_date) = date($3)))
22. 5847.933 5847.933 ↓ 0.0 0 5847933

Index Scan using item_class_cd_pkey on item_class_cd (cost=0.00..0.30 rows=1 width=31) (actual time=0.001..0.001 rows=0 loops=5847933)

  • Index Cond: (iin.class_cd_id = item_class_cd.class_cd_id)
  • Filter: ((item_class_cd.division)::text = 'BSM'::text)
23. 17543.799 17543.799 ↑ 1.0 1 5847933

Index Scan using item_size_cd_pkey on item_size_cd (cost=0.00..0.27 rows=1 width=25) (actual time=0.002..0.003 rows=1 loops=5847933)

  • Index Cond: (iis.size_cd_id = item_size_cd.size_cd_id)
  • Filter: ((item_size_cd.division)::text = 'BSM'::text)
24. 17543.799 17543.799 ↑ 1.0 1 5847933

Index Scan using item_color_cd_pkey on item_color_cd (cost=0.00..0.27 rows=1 width=25) (actual time=0.002..0.003 rows=1 loops=5847933)

  • Index Cond: (iis.color_cd_id = item_color_cd.color_cd_id)
  • Filter: ((item_color_cd.division)::text = 'BSM'::text)
25. 29239.665 29239.665 ↑ 1.0 1 5847933

Index Scan using ord_line_pkey on ord_line (cost=0.00..14.94 rows=1 width=16) (actual time=0.004..0.005 rows=1 loops=5847933)

  • Index Cond: (ord_line.ord_line_id = ls.ord_line_id)
  • Filter: ((ord_line.division)::text = 'BSM'::text)