#	exclusive	inclusive	rows x	rows	loops	node
1.	0.018	5221.571	↑ 5.1	13	1	→ Sort (cost=35435.87..35436.03 rows=66 width=734) (actual time=5221.571..5221.571 rows=13 loops=1) Sort Key: a.month Sort Method: quicksort Memory: 27kB
2.	0.058	5221.553	↑ 5.1	13	1	→ HashAggregate (cost=35433.21..35433.87 rows=66 width=734) (actual time=5221.548..5221.553 rows=13 loops=1)
3.	0.005	5221.495	↑ 5.1	13	1	→ Append (cost=1405.02..35429.09 rows=66 width=734) (actual time=879.956..5221.495 rows=13 loops=1)
4.	0.030	2667.205	↑ 5.4	12	1	→ Merge Right Join (cost=1405.02..17878.71 rows=65 width=744) (actual time=879.956..2667.205 rows=12 loops=1) Merge Cond: (fstw.month = a.month)
5.	275.148	2667.151	↑ 119.7	9	1	→ GroupAggregate (cost=1403.69..17862.93 rows=1077 width=75) (actual time=879.931..2667.151 rows=9 loops=1)
6.	377.601	2392.003	↓ 24.3	90018	1	→ Nested Loop (cost=1403.69..17224.89 rows=3712 width=75) (actual time=609.136..2392.003 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)))
7.	0.056	0.214	↓ 12.0	12	1	→ Nested Loop (cost=4.21..5.46 rows=1 width=70) (actual time=0.053..0.214 rows=12 loops=1)
8.	0.048	0.110	↓ 12.0	12	1	→ Merge Join (cost=4.21..4.28 rows=1 width=40) (actual time=0.046..0.110 rows=12 loops=1) Merge Cond: (fstw.month = fstd.month)
9.	0.017	0.027	↓ 12.0	12	1	→ Sort (cost=1.19..1.19 rows=1 width=28) (actual time=0.021..0.027 rows=12 loops=1) Sort Key: fstw.month Sort Method: quicksort Memory: 25kB
10.	0.010	0.010	↓ 12.0	12	1	→ Seq Scan on ft_sys_tax_weeks fstw (cost=0.00..1.18 rows=1 width=28) (actual time=0.008..0.010 rows=12 loops=1) Filter: ((month >= 1) AND (month <= 12))
11.	0.020	0.035	↑ 1.0	12	1	→ Sort (cost=3.02..3.05 rows=12 width=16) (actual time=0.023..0.035 rows=12 loops=1) Sort Key: fstd.month Sort Method: quicksort Memory: 25kB
12.	0.015	0.015	↑ 1.0	12	1	→ Seq Scan on ft_sys_taxyear_details fstd (cost=0.00..2.80 rows=12 width=16) (actual time=0.013..0.015 rows=12 loops=1) Filter: (taxyear_id = 12)
13.	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))
14.	578.452	2014.188	↓ 2.6	90018	12	→ Hash Join (cost=1399.48..15551.14 rows=35122 width=81) (actual time=8.078..167.849 rows=90018 loops=12) Hash Cond: (fenvy.ee_id = fee.ee_id)
15.	1426.956	1426.956	↓ 1.1	100066	12	→ Seq Scan on ft_ee_net_values_ytd fenvy (cost=0.00..12377.39 rows=94870 width=85) (actual time=7.342..118.913 rows=100066 loops=12) Filter: (((process_year)::text = '20122013'::text) AND (ee_er_id = 100))
16.	1.623	8.780	↑ 1.0	10002	1	→ Hash (cost=1274.33..1274.33 rows=10012 width=8) (actual time=8.780..8.780 rows=10002 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 391kB
17.	6.526	7.157	↑ 1.0	10002	1	→ Bitmap Heap Scan on ft_employees fee (cost=234.28..1274.33 rows=10012 width=8) (actual time=0.670..7.157 rows=10002 loops=1) Recheck Cond: (ee_er_id = 100) Filter: ((ee_sys_status)::text <> 'D'::text)
18.	0.631	0.631	↑ 1.0	10002	1	→ Bitmap Index Scan on ft_ee_u1 (cost=0.00..231.78 rows=10070 width=0) (actual time=0.631..0.631 rows=10002 loops=1) Index Cond: (ee_er_id = 100)
19.	0.019	0.024	↑ 1.0	12	1	→ Sort (cost=1.34..1.37 rows=12 width=4) (actual time=0.019..0.024 rows=12 loops=1) Sort Key: a.month Sort Method: quicksort Memory: 25kB
20.	0.005	0.005	↑ 1.0	12	1	→ Seq Scan on ft_sys_tax_weeks a (cost=0.00..1.12 rows=12 width=4) (actual time=0.003..0.005 rows=12 loops=1)
21.	265.008	2554.285	↑ 1.0	1	1	→ Aggregate (cost=17549.45..17549.72 rows=1 width=58) (actual time=2554.285..2554.285 rows=1 loops=1)
22.	381.701	2289.277	↓ 24.3	90018	1	→ Nested Loop (cost=1400.67..17224.65 rows=3712 width=58) (actual time=585.089..2289.277 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)))
23.	0.064	0.188	↓ 12.0	12	1	→ Nested Loop (cost=1.19..5.22 rows=1 width=70) (actual time=0.059..0.188 rows=12 loops=1)
24.	0.036	0.076	↓ 12.0	12	1	→ Hash Join (cost=1.19..4.05 rows=1 width=40) (actual time=0.051..0.076 rows=12 loops=1) Hash Cond: (fstd.month = fstw.month)
25.	0.028	0.028	↑ 1.0	12	1	→ Seq Scan on ft_sys_taxyear_details fstd (cost=0.00..2.80 rows=12 width=16) (actual time=0.016..0.028 rows=12 loops=1) Filter: (taxyear_id = 12)
26.	0.004	0.012	↓ 12.0	12	1	→ Hash (cost=1.18..1.18 rows=1 width=28) (actual time=0.012..0.012 rows=12 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 1kB
27.	0.008	0.008	↓ 12.0	12	1	→ Seq Scan on ft_sys_tax_weeks fstw (cost=0.00..1.18 rows=1 width=28) (actual time=0.004..0.008 rows=12 loops=1) Filter: ((month >= 1) AND (month <= 12))
28.	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.003..0.004 rows=1 loops=12) Filter: ((taxyear_id = 12) AND ((taxyear)::text = '20122013'::text))
29.	609.977	1907.388	↓ 2.6	90018	12	→ Hash Join (cost=1399.48..15551.14 rows=35122 width=77) (actual time=1.998..158.949 rows=90018 loops=12) Hash Cond: (fenvy.ee_id = fee.ee_id)
30.	1289.268	1289.268	↓ 1.1	100066	12	→ Seq Scan on ft_ee_net_values_ytd fenvy (cost=0.00..12377.39 rows=94870 width=85) (actual time=1.315..107.439 rows=100066 loops=12) Filter: ((ee_er_id = 100) AND ((process_year)::text = '20122013'::text))
31.	1.622	8.143	↑ 1.0	10002	1	→ Hash (cost=1274.33..1274.33 rows=10012 width=8) (actual time=8.143..8.143 rows=10002 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 391kB
32.	5.949	6.521	↑ 1.0	10002	1	→ Bitmap Heap Scan on ft_employees fee (cost=234.28..1274.33 rows=10012 width=8) (actual time=0.611..6.521 rows=10002 loops=1) Recheck Cond: (ee_er_id = 100) Filter: ((ee_sys_status)::text <> 'D'::text)
33.	0.572	0.572	↑ 1.0	10002	1	→ Bitmap Index Scan on ft_ee_u1 (cost=0.00..231.78 rows=10070 width=0) (actual time=0.572..0.572 rows=10002 loops=1) Index Cond: (ee_er_id = 100)

Sort  (cost=35435.87..35436.03 rows=66 width=734) (actual time=5221.571..5221.571 rows=13 loops=1)

  Sort Key: a.month

  Sort Method: quicksort  Memory: 27kB

  ->  HashAggregate  (cost=35433.21..35433.87 rows=66 width=734) (actual time=5221.548..5221.553 rows=13 loops=1)

        ->  Append  (cost=1405.02..35429.09 rows=66 width=734) (actual time=879.956..5221.495 rows=13 loops=1)

              ->  Merge Right Join  (cost=1405.02..17878.71 rows=65 width=744) (actual time=879.956..2667.205 rows=12 loops=1)

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

                    ->  GroupAggregate  (cost=1403.69..17862.93 rows=1077 width=75) (actual time=879.931..2667.151 rows=9 loops=1)

                          ->  Nested Loop  (cost=1403.69..17224.89 rows=3712 width=75) (actual time=609.136..2392.003 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.053..0.214 rows=12 loops=1)

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

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

                                            ->  Sort  (cost=1.19..1.19 rows=1 width=28) (actual time=0.021..0.027 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.008..0.010 rows=12 loops=1)

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

                                            ->  Sort  (cost=3.02..3.05 rows=12 width=16) (actual time=0.023..0.035 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.013..0.015 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.14 rows=35122 width=81) (actual time=8.078..167.849 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.39 rows=94870 width=85) (actual time=7.342..118.913 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.780..8.780 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.670..7.157 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.631..0.631 rows=10002 loops=1)

                                                        Index Cond: (ee_er_id = 100)

                    ->  Sort  (cost=1.34..1.37 rows=12 width=4) (actual time=0.019..0.024 rows=12 loops=1)

                          Sort Key: a.month

                          Sort Method: quicksort  Memory: 25kB

                          ->  Seq Scan on ft_sys_tax_weeks a  (cost=0.00..1.12 rows=12 width=4) (actual time=0.003..0.005 rows=12 loops=1)

              ->  Aggregate  (cost=17549.45..17549.72 rows=1 width=58) (actual time=2554.285..2554.285 rows=1 loops=1)

                    ->  Nested Loop  (cost=1400.67..17224.65 rows=3712 width=58) (actual time=585.089..2289.277 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=1.19..5.22 rows=1 width=70) (actual time=0.059..0.188 rows=12 loops=1)

                                ->  Hash Join  (cost=1.19..4.05 rows=1 width=40) (actual time=0.051..0.076 rows=12 loops=1)

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

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

                                            Filter: (taxyear_id = 12)

                                      ->  Hash  (cost=1.18..1.18 rows=1 width=28) (actual time=0.012..0.012 rows=12 loops=1)

                                            Buckets: 1024  Batches: 1  Memory Usage: 1kB

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

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

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

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

                          ->  Hash Join  (cost=1399.48..15551.14 rows=35122 width=77) (actual time=1.998..158.949 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.39 rows=94870 width=85) (actual time=1.315..107.439 rows=100066 loops=12)

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

                                ->  Hash  (cost=1274.33..1274.33 rows=10012 width=8) (actual time=8.143..8.143 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.611..6.521 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.572..0.572 rows=10002 loops=1)

                                                  Index Cond: (ee_er_id = 100)

Per node type stats

node type	count	sum of times	% of query
Aggregate	1	265.008 ms	5.1 %
Append	1	0.005 ms	0.0 %
Bitmap Heap Scan	2	12.475 ms	0.2 %
Bitmap Index Scan	2	1.203 ms	0.0 %
GroupAggregate	1	275.148 ms	5.3 %
Hash	3	3.249 ms	0.1 %
Hash Join	3	1188.465 ms	22.8 %
HashAggregate	1	0.058 ms	0.0 %
Merge Join	1	0.048 ms	0.0 %
Merge Right Join	1	0.030 ms	0.0 %
Nested Loop	4	759.422 ms	14.5 %
Seq Scan	9	2716.386 ms	52.0 %
Sort	4	0.074 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	2	2716.224 ms	52.0 %
Seq Scan	2	2716.224 ms	100.0 %
ft_employees	2	12.475 ms	0.2 %
Bitmap Heap Scan	2	12.475 ms	100.0 %
ft_sys_tax_weeks	3	0.023 ms	0.0 %
Seq Scan	3	0.023 ms	100.0 %
ft_sys_taxyear	2	0.096 ms	0.0 %
Seq Scan	2	0.096 ms	100.0 %
ft_sys_taxyear_details	2	0.043 ms	0.0 %
Seq Scan	2	0.043 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: 8Yg : NEW_P32_V_REPORT

Colorize:

Columns visibility:

Per node type stats

Per table stats