#	exclusive	inclusive	rows x	rows	loops	node
1.	74.139	2368.044	↑ 119.7	9	1	→ GroupAggregate (cost=1403.69..17327.69 rows=1077 width=29) (actual time=831.073..2368.044 rows=9 loops=1)
2.	373.708	2293.905	↓ 24.2	90018	1	→ Nested Loop (cost=1403.69..17225.02 rows=3713 width=29) (actual time=591.744..2293.905 rows=90018 loops=1) Join Filter: (((fenvy.ee_ssp <= 0::numeric) AND (((fenvy.process_period = fstw.month) AND ((fenvy.ee_pay_freq)::text = 'M'::text)) OR ((fenvy.process_period >= fstw.wk_start) AND (fenvy.process_period <= fstw.wk_end) AND ((fenvy.ee_pay_freq)::text = 'W'::text)) OR ((fenvy.process_period >= fstw.tw_start) AND (fenvy.process_period <= fstw.tw_end) AND ((fenvy.ee_pay_freq)::text = 'N'::text)) OR ((fenvy.process_period >= fstw.fw_start) AND (fenvy.process_period <= fstw.fw_end) AND ((fenvy.ee_pay_freq)::text = 'F'::text)))) OR ((fenvy.ee_ssp > 0::numeric) AND (fenvy.process_date >= fstd.month_start_date) AND (fenvy.process_date <= fstd.month_end_date)))
3.	0.056	0.245	↓ 12.0	12	1	→ Nested Loop (cost=4.21..5.46 rows=1 width=70) (actual time=0.095..0.245 rows=12 loops=1)
4.	0.047	0.141	↓ 12.0	12	1	→ Merge Join (cost=4.21..4.28 rows=1 width=40) (actual time=0.084..0.141 rows=12 loops=1) Merge Cond: (fstw.month = fstd.month)
5.	0.024	0.043	↓ 12.0	12	1	→ Sort (cost=1.19..1.19 rows=1 width=28) (actual time=0.041..0.043 rows=12 loops=1) Sort Key: fstw.month Sort Method: quicksort Memory: 25kB
6.	0.019	0.019	↓ 12.0	12	1	→ Seq Scan on ft_sys_tax_weeks fstw (cost=0.00..1.18 rows=1 width=28) (actual time=0.013..0.019 rows=12 loops=1) Filter: ((month >= 1) AND (month <= 12))
7.	0.022	0.051	↑ 1.0	12	1	→ Sort (cost=3.02..3.05 rows=12 width=16) (actual time=0.038..0.051 rows=12 loops=1) Sort Key: fstd.month Sort Method: quicksort Memory: 25kB
8.	0.029	0.029	↑ 1.0	12	1	→ Seq Scan on ft_sys_taxyear_details fstd (cost=0.00..2.80 rows=12 width=16) (actual time=0.024..0.029 rows=12 loops=1) Filter: (taxyear_id = 12)
9.	0.048	0.048	↑ 1.0	1	12	→ Seq Scan on ft_sys_taxyear fst (cost=0.00..1.17 rows=1 width=38) (actual time=0.004..0.004 rows=1 loops=12) Filter: (((taxyear)::text = '20122013'::text) AND (taxyear_id = 12))
10.	476.772	1919.952	↓ 2.6	90018	12	→ Hash Join (cost=1399.48..15551.22 rows=35123 width=41) (actual time=8.537..159.996 rows=90018 loops=12) Hash Cond: (fenvy.ee_id = fee.ee_id)
11.	1434.348	1434.348	↓ 1.1	100066	12	→ Seq Scan on ft_ee_net_values_ytd fenvy (cost=0.00..12377.43 rows=94872 width=45) (actual time=7.796..119.529 rows=100066 loops=12) Filter: (((process_year)::text = '20122013'::text) AND (ee_er_id = 100))
12.	1.590	8.832	↑ 1.0	10002	1	→ Hash (cost=1274.33..1274.33 rows=10012 width=8) (actual time=8.832..8.832 rows=10002 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 391kB
13.	6.610	7.242	↑ 1.0	10002	1	→ Bitmap Heap Scan on ft_employees fee (cost=234.28..1274.33 rows=10012 width=8) (actual time=0.672..7.242 rows=10002 loops=1) Recheck Cond: (ee_er_id = 100) Filter: ((ee_sys_status)::text <> 'D'::text)
14.	0.632	0.632	↑ 1.0	10002	1	→ Bitmap Index Scan on ft_ee_u1 (cost=0.00..231.78 rows=10070 width=0) (actual time=0.632..0.632 rows=10002 loops=1) Index Cond: (ee_er_id = 100)

GroupAggregate  (cost=1403.69..17327.69 rows=1077 width=29) (actual time=831.073..2368.044 rows=9 loops=1)

  ->  Nested Loop  (cost=1403.69..17225.02 rows=3713 width=29) (actual time=591.744..2293.905 rows=90018 loops=1)

        Join Filter: (((fenvy.ee_ssp <= 0::numeric) AND (((fenvy.process_period = fstw.month) AND ((fenvy.ee_pay_freq)::text = 'M'::text)) OR ((fenvy.process_period >= fstw.wk_start) AND (fenvy.process_period <= fstw.wk_end) AND ((fenvy.ee_pay_freq)::text = 'W'::text)) OR ((fenvy.process_period >= fstw.tw_start) AND (fenvy.process_period <= fstw.tw_end) AND ((fenvy.ee_pay_freq)::text = 'N'::text)) OR ((fenvy.process_period >= fstw.fw_start) AND (fenvy.process_period <= fstw.fw_end) AND ((fenvy.ee_pay_freq)::text = 'F'::text)))) OR ((fenvy.ee_ssp > 0::numeric) AND (fenvy.process_date >= fstd.month_start_date) AND (fenvy.process_date <= fstd.month_end_date)))

        ->  Nested Loop  (cost=4.21..5.46 rows=1 width=70) (actual time=0.095..0.245 rows=12 loops=1)

              ->  Merge Join  (cost=4.21..4.28 rows=1 width=40) (actual time=0.084..0.141 rows=12 loops=1)

                    Merge Cond: (fstw.month = fstd.month)

                    ->  Sort  (cost=1.19..1.19 rows=1 width=28) (actual time=0.041..0.043 rows=12 loops=1)

                          Sort Key: fstw.month

                          Sort Method: quicksort  Memory: 25kB

                          ->  Seq Scan on ft_sys_tax_weeks fstw  (cost=0.00..1.18 rows=1 width=28) (actual time=0.013..0.019 rows=12 loops=1)

                                Filter: ((month >= 1) AND (month <= 12))

                    ->  Sort  (cost=3.02..3.05 rows=12 width=16) (actual time=0.038..0.051 rows=12 loops=1)

                          Sort Key: fstd.month

                          Sort Method: quicksort  Memory: 25kB

                          ->  Seq Scan on ft_sys_taxyear_details fstd  (cost=0.00..2.80 rows=12 width=16) (actual time=0.024..0.029 rows=12 loops=1)

                                Filter: (taxyear_id = 12)

              ->  Seq Scan on ft_sys_taxyear fst  (cost=0.00..1.17 rows=1 width=38) (actual time=0.004..0.004 rows=1 loops=12)

                    Filter: (((taxyear)::text = '20122013'::text) AND (taxyear_id = 12))

        ->  Hash Join  (cost=1399.48..15551.22 rows=35123 width=41) (actual time=8.537..159.996 rows=90018 loops=12)

              Hash Cond: (fenvy.ee_id = fee.ee_id)

              ->  Seq Scan on ft_ee_net_values_ytd fenvy  (cost=0.00..12377.43 rows=94872 width=45) (actual time=7.796..119.529 rows=100066 loops=12)

                    Filter: (((process_year)::text = '20122013'::text) AND (ee_er_id = 100))

              ->  Hash  (cost=1274.33..1274.33 rows=10012 width=8) (actual time=8.832..8.832 rows=10002 loops=1)

                    Buckets: 1024  Batches: 1  Memory Usage: 391kB

                    ->  Bitmap Heap Scan on ft_employees fee  (cost=234.28..1274.33 rows=10012 width=8) (actual time=0.672..7.242 rows=10002 loops=1)

                          Recheck Cond: (ee_er_id = 100)

                          Filter: ((ee_sys_status)::text <> 'D'::text)

                          ->  Bitmap Index Scan on ft_ee_u1  (cost=0.00..231.78 rows=10070 width=0) (actual time=0.632..0.632 rows=10002 loops=1)

                                Index Cond: (ee_er_id = 100)

Per node type stats

node type	count	sum of times	% of query
Bitmap Heap Scan	1	6.610 ms	0.3 %
Bitmap Index Scan	1	0.632 ms	0.0 %
GroupAggregate	1	74.139 ms	3.1 %
Hash	1	1.590 ms	0.1 %
Hash Join	1	476.772 ms	20.1 %
Merge Join	1	0.047 ms	0.0 %
Nested Loop	2	373.764 ms	15.8 %
Seq Scan	4	1434.444 ms	60.6 %
Sort	2	0.046 ms	0.0 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
ft_ee_net_values_ytd	1	1434.348 ms	60.6 %
Seq Scan	1	1434.348 ms	100.0 %
ft_employees	1	6.610 ms	0.3 %
Bitmap Heap Scan	1	6.610 ms	100.0 %
ft_sys_tax_weeks	1	0.019 ms	0.0 %
Seq Scan	1	0.019 ms	100.0 %
ft_sys_taxyear	1	0.048 ms	0.0 %
Seq Scan	1	0.048 ms	100.0 %
ft_sys_taxyear_details	1	0.029 ms	0.0 %
Seq Scan	1	0.029 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: BhB : TEST_P32

Colorize:

Columns visibility:

Per node type stats

Per table stats