#	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)

"HashAggregate  (cost=9388.82..9388.88 rows=1 width=311) (actual time=187787.183..187788.001 rows=153 loops=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))"
"        ->  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)"
"              ->  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)"
"                    ->  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)"
"                          ->  Nested Loop  (cost=9266.68..9372.92 rows=1 width=294) (actual time=92.183..61183.081 rows=5847933 loops=1)"
"                                ->  Nested Loop  (cost=67.45..91.07 rows=1 width=75) (actual time=0.819..58.193 rows=8463 loops=1)"
"                                      ->  Nested Loop  (cost=67.45..75.24 rows=1 width=51) (actual time=0.784..3.894 rows=360 loops=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)"
"                                            ->  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))"
"                                                  ->  BitmapAnd  (cost=67.45..67.45 rows=1 width=0) (actual time=0.687..0.687 rows=0 loops=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)"
"                                                        ->  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)"
"                                      ->  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)"
"                                ->  HashAggregate  (cost=9199.23..9281.69 rows=8 width=66) (actual time=0.020..6.298 rows=691 loops=8463)"
"                                      ->  Nested Loop  (cost=0.01..9198.91 rows=8 width=66) (actual time=1.111..89.484 rows=691 loops=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)))"
"                                            ->  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)"
"                                      SubPlan 1"
"                                        ->  Aggregate  (cost=10.25..10.26 rows=1 width=8) (actual time=0.005..0.006 rows=1 loops=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)))"
"                          ->  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)"
"                    ->  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)"
"              ->  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)"
"        ->  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)"
"Total runtime: 187788.419 ms"

Per node type stats

node type	count	sum of times	% of query
Aggregate	1	11695.866 ms	6.2 %
Bitmap Heap Scan	1	2.628 ms	0.0 %
Bitmap Index Scan	2	0.653 ms	0.0 %
BitmapAnd	1	0.034 ms	0.0 %
HashAggregate	2	18125.802 ms	9.7 %
Index Scan	9	93691.194 ms	49.9 %
Nested Loop	5	19229.384 ms	10.2 %
Nested Loop Left Join	3	45042.440 ms	24.0 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
item_class_cd	1	5847.933 ms	3.1 %
Index Scan	1	5847.933 ms	100.0 %
item_color_cd	1	17543.799 ms	9.3 %
Index Scan	1	17543.799 ms	100.0 %
item_dept_cd	1	0.047 ms	0.0 %
Index Scan	1	0.047 ms	100.0 %
item_item_num	1	2.628 ms	0.0 %
Bitmap Heap Scan	1	2.628 ms	100.0 %
item_size_cd	1	17543.799 ms	9.3 %
Index Scan	1	17543.799 ms	100.0 %
item_sku	1	37.440 ms	0.0 %
Index Scan	1	37.440 ms	100.0 %
ledger_demand	1	79.178 ms	0.0 %
Index Scan	1	79.178 ms	100.0 %
ledger_discount	1	23391.732 ms	12.5 %
Index Scan	1	23391.732 ms	100.0 %
ord_header	1	7.601 ms	0.0 %
Index Scan	1	7.601 ms	100.0 %
ord_line	1	29239.665 ms	15.6 %
Index Scan	1	29239.665 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: MwR

Colorize:

Columns visibility:

Per node type stats

Per table stats