#	exclusive	inclusive	rows x	rows	loops	node
1.	6.079	338.254	↓ 27.7	83	1	→ GroupAggregate (cost=201.70..74227.78 rows=3 width=43) (actual time=13.893..338.254 rows=83 loops=1)
2.	3.689	11.297	↓ 703.3	2110	1	→ Sort (cost=201.70..201.70 rows=3 width=43) (actual time=11.175..11.297 rows=2110 loops=1) Sort Key: l.master_code Sort Method: quicksort Memory: 266kB
3.	1.951	7.608	↓ 703.3	2110	1	→ Nested Loop Left Join (cost=6.68..201.67 rows=3 width=43) (actual time=0.140..7.608 rows=2110 loops=1)
4.	0.473	0.473	↓ 81.0	81	1	→ Index Scan using tm_sale_date on transaction_master m (cost=0.00..8.46 rows=1 width=48) (actual time=0.049..0.473 rows=81 loops=1) Index Cond: ((sale_date >= '2012-08-07'::date) AND (sale_date <= '2012-08-07'::date)) Filter: ((transaction_type)::text = ANY ('{POSSALE,POSCN,POSREF,GRV,IBTIN,IBTOUT,STKADJ}'::text[]))
5.	1.620	5.184	↑ 1.8	26	81	→ Bitmap Heap Scan on transaction_line_items l (cost=6.68..192.62 rows=47 width=69) (actual time=0.053..0.064 rows=26 loops=81) Recheck Cond: ((m.guid)::text = (link_guid)::text)
6.	3.564	3.564	↑ 1.8	26	81	→ Bitmap Index Scan on tl_link_guid (cost=0.00..6.67 rows=47 width=0) (actual time=0.044..0.044 rows=26 loops=81) Index Cond: ((m.guid)::text = (link_guid)::text)
7.						SubPlan (forGroupAggregate)
8.	81.423	320.878	↑ 1.0	1	83	→ Aggregate (cost=24675.19..24675.20 rows=1 width=3) (actual time=3.865..3.866 rows=1 loops=83)
9.	46.810	239.455	↑ 2.1	2592	83	→ Nested Loop (cost=19.76..24661.68 rows=5407 width=3) (actual time=0.115..2.885 rows=2592 loops=83)
10.	2.573	2.573	↑ 2.2	12	83	→ Index Scan using stockcodes_master_master_code_idx on stockcodes_master (cost=0.00..42.77 rows=26 width=12) (actual time=0.020..0.031 rows=12 loops=83) Index Cond: ((master_code)::text = (l.master_code)::text)
11.	128.092	190.072	↑ 1.1	208	1033	→ Bitmap Heap Scan on stock_on_hand (cost=19.76..943.92 rows=237 width=15) (actual time=0.077..0.184 rows=208 loops=1033) Recheck Cond: ((generated_code)::text = (stockcodes_master.generated_code)::text)
12.	61.980	61.980	↑ 1.1	220	1033	→ Bitmap Index Scan on stock_on_hand_pkey (cost=0.00..19.70 rows=237 width=0) (actual time=0.060..0.060 rows=220 loops=1033) Index Cond: ((generated_code)::text = (stockcodes_master.generated_code)::text)

GroupAggregate  (cost=201.70..74227.78 rows=3 width=43) (actual time=13.893..338.254 rows=83 loops=1)
  ->  Sort  (cost=201.70..201.70 rows=3 width=43) (actual time=11.175..11.297 rows=2110 loops=1)
        Sort Key: l.master_code
        Sort Method: quicksort  Memory: 266kB
        ->  Nested Loop Left Join  (cost=6.68..201.67 rows=3 width=43) (actual time=0.140..7.608 rows=2110 loops=1)
              ->  Index Scan using tm_sale_date on transaction_master m  (cost=0.00..8.46 rows=1 width=48) (actual time=0.049..0.473 rows=81 loops=1)
                    Index Cond: ((sale_date >= '2012-08-07'::date) AND (sale_date <= '2012-08-07'::date))
                    Filter: ((transaction_type)::text = ANY ('{POSSALE,POSCN,POSREF,GRV,IBTIN,IBTOUT,STKADJ}'::text[]))
              ->  Bitmap Heap Scan on transaction_line_items l  (cost=6.68..192.62 rows=47 width=69) (actual time=0.053..0.064 rows=26 loops=81)
                    Recheck Cond: ((m.guid)::text = (link_guid)::text)
                    ->  Bitmap Index Scan on tl_link_guid  (cost=0.00..6.67 rows=47 width=0) (actual time=0.044..0.044 rows=26 loops=81)
                          Index Cond: ((m.guid)::text = (link_guid)::text)
  SubPlan 1
    ->  Aggregate  (cost=24675.19..24675.20 rows=1 width=3) (actual time=3.865..3.866 rows=1 loops=83)
          ->  Nested Loop  (cost=19.76..24661.68 rows=5407 width=3) (actual time=0.115..2.885 rows=2592 loops=83)
                ->  Index Scan using stockcodes_master_master_code_idx on stockcodes_master  (cost=0.00..42.77 rows=26 width=12) (actual time=0.020..0.031 rows=12 loops=83)
                      Index Cond: ((master_code)::text = (l.master_code)::text)
                ->  Bitmap Heap Scan on stock_on_hand  (cost=19.76..943.92 rows=237 width=15) (actual time=0.077..0.184 rows=208 loops=1033)
                      Recheck Cond: ((generated_code)::text = (stockcodes_master.generated_code)::text)
                      ->  Bitmap Index Scan on stock_on_hand_pkey  (cost=0.00..19.70 rows=237 width=0) (actual time=0.060..0.060 rows=220 loops=1033)
                            Index Cond: ((generated_code)::text = (stockcodes_master.generated_code)::text)
Total runtime: 338.628 ms

Per node type stats

node type	count	sum of times	% of query
Aggregate	1	81.423 ms	24.1 %
Bitmap Heap Scan	2	129.712 ms	38.3 %
Bitmap Index Scan	2	65.544 ms	19.4 %
GroupAggregate	1	6.079 ms	1.8 %
Index Scan	2	3.046 ms	0.9 %
Nested Loop	1	46.810 ms	13.8 %
Nested Loop Left Join	1	1.951 ms	0.6 %
Sort	1	3.689 ms	1.1 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
stock_on_hand	1	128.092 ms	37.9 %
Bitmap Heap Scan	1	128.092 ms	100.0 %
stockcodes_master	1	2.573 ms	0.8 %
Index Scan	1	2.573 ms	100.0 %
transaction_line_items	1	1.620 ms	0.5 %
Bitmap Heap Scan	1	1.620 ms	100.0 %
transaction_master	1	0.473 ms	0.1 %
Index Scan	1	0.473 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: oZX

Colorize:

Columns visibility:

Per node type stats

Per table stats