#	exclusive	inclusive	rows x	rows	loops	node
1.	0.017	5171.869	↑ 5.1	13	1	→ Sort (cost=35436.31..35436.48 rows=66 width=734) (actual time=5171.867..5171.869 rows=13 loops=1) Sort Key: a.month Sort Method: quicksort Memory: 27kB
2.	0.059	5171.852	↑ 5.1	13	1	→ HashAggregate (cost=35433.66..35434.32 rows=66 width=734) (actual time=5171.845..5171.852 rows=13 loops=1)
3.	0.007	5171.793	↑ 5.1	13	1	→ Append (cost=1405.02..35429.53 rows=66 width=734) (actual time=879.173..5171.793 rows=13 loops=1)
4.	0.024	2659.361	↑ 5.4	12	1	→ Merge Right Join (cost=1405.02..17878.93 rows=65 width=744) (actual time=879.173..2659.361 rows=12 loops=1) Merge Cond: (fstw.month = a.month)
5.	273.636	2659.314	↑ 119.7	9	1	→ GroupAggregate (cost=1403.69..17863.16 rows=1077 width=75) (actual time=879.151..2659.314 rows=9 loops=1)
6.	372.602	2385.678	↓ 24.2	90018	1	→ Nested Loop (cost=1403.69..17225.02 rows=3713 width=75) (actual time=608.500..2385.678 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.067	0.232	↓ 12.0	12	1	→ Nested Loop (cost=4.21..5.46 rows=1 width=70) (actual time=0.053..0.232 rows=12 loops=1)
8.	0.048	0.105	↓ 12.0	12	1	→ Merge Join (cost=4.21..4.28 rows=1 width=40) (actual time=0.047..0.105 rows=12 loops=1) Merge Cond: (fstw.month = fstd.month)
9.	0.014	0.024	↓ 12.0	12	1	→ Sort (cost=1.19..1.19 rows=1 width=28) (actual time=0.021..0.024 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.007..0.010 rows=12 loops=1) Filter: ((month >= 1) AND (month <= 12))
11.	0.019	0.033	↑ 1.0	12	1	→ Sort (cost=3.02..3.05 rows=12 width=16) (actual time=0.023..0.033 rows=12 loops=1) Sort Key: fstd.month Sort Method: quicksort Memory: 25kB
12.	0.014	0.014	↑ 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.014 rows=12 loops=1) Filter: (taxyear_id = 12)
13.	0.060	0.060	↑ 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.005 rows=1 loops=12) Filter: (((taxyear)::text = '20122013'::text) AND (taxyear_id = 12))
14.	580.332	2012.844	↓ 2.6	90018	12	→ Hash Join (cost=1399.48..15551.22 rows=35123 width=81) (actual time=8.091..167.737 rows=90018 loops=12) Hash Cond: (fenvy.ee_id = fee.ee_id)
15.	1423.728	1423.728	↓ 1.1	100066	12	→ Seq Scan on ft_ee_net_values_ytd fenvy (cost=0.00..12377.43 rows=94872 width=85) (actual time=7.353..118.644 rows=100066 loops=12) Filter: (((process_year)::text = '20122013'::text) AND (ee_er_id = 100))
16.	1.648	8.784	↑ 1.0	10002	1	→ Hash (cost=1274.33..1274.33 rows=10012 width=8) (actual time=8.784..8.784 rows=10002 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 391kB
17.	6.519	7.136	↑ 1.0	10002	1	→ Bitmap Heap Scan on ft_employees fee (cost=234.28..1274.33 rows=10012 width=8) (actual time=0.656..7.136 rows=10002 loops=1) Recheck Cond: (ee_er_id = 100) Filter: ((ee_sys_status)::text <> 'D'::text)
18.	0.617	0.617	↑ 1.0	10002	1	→ Bitmap Index Scan on ft_ee_u1 (cost=0.00..231.78 rows=10070 width=0) (actual time=0.617..0.617 rows=10002 loops=1) Index Cond: (ee_er_id = 100)
19.	0.019	0.023	↑ 1.0	12	1	→ Sort (cost=1.34..1.37 rows=12 width=4) (actual time=0.018..0.023 rows=12 loops=1) Sort Key: a.month Sort Method: quicksort Memory: 25kB
20.	0.004	0.004	↑ 1.0	12	1	→ Seq Scan on ft_sys_tax_weeks a (cost=0.00..1.12 rows=12 width=4) (actual time=0.002..0.004 rows=12 loops=1)
21.	263.176	2512.425	↑ 1.0	1	1	→ Aggregate (cost=17549.67..17549.94 rows=1 width=58) (actual time=2512.425..2512.425 rows=1 loops=1)
22.	373.799	2249.249	↓ 24.2	90018	1	→ Nested Loop (cost=1400.67..17224.79 rows=3713 width=58) (actual time=567.625..2249.249 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.053	0.186	↓ 12.0	12	1	→ Nested Loop (cost=1.19..5.22 rows=1 width=70) (actual time=0.057..0.186 rows=12 loops=1)
24.	0.039	0.073	↓ 12.0	12	1	→ Hash Join (cost=1.19..4.05 rows=1 width=40) (actual time=0.048..0.073 rows=12 loops=1) Hash Cond: (fstd.month = fstw.month)
25.	0.023	0.023	↑ 1.0	12	1	→ Seq Scan on ft_sys_taxyear_details fstd (cost=0.00..2.80 rows=12 width=16) (actual time=0.015..0.023 rows=12 loops=1) Filter: (taxyear_id = 12)
26.	0.005	0.011	↓ 12.0	12	1	→ Hash (cost=1.18..1.18 rows=1 width=28) (actual time=0.011..0.011 rows=12 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 1kB
27.	0.006	0.006	↓ 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.006 rows=12 loops=1) Filter: ((month >= 1) AND (month <= 12))
28.	0.060	0.060	↑ 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.005 rows=1 loops=12) Filter: (((taxyear)::text = '20122013'::text) AND (taxyear_id = 12))
29.	600.959	1875.264	↓ 2.6	90018	12	→ Hash Join (cost=1399.48..15551.22 rows=35123 width=77) (actual time=2.004..156.272 rows=90018 loops=12) Hash Cond: (fenvy.ee_id = fee.ee_id)
30.	1266.132	1266.132	↓ 1.1	100066	12	→ Seq Scan on ft_ee_net_values_ytd fenvy (cost=0.00..12377.43 rows=94872 width=85) (actual time=1.318..105.511 rows=100066 loops=12) Filter: ((ee_er_id = 100) AND ((process_year)::text = '20122013'::text))
31.	1.541	8.173	↑ 1.0	10002	1	→ Hash (cost=1274.33..1274.33 rows=10012 width=8) (actual time=8.173..8.173 rows=10002 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 391kB
32.	6.073	6.632	↑ 1.0	10002	1	→ Bitmap Heap Scan on ft_employees fee (cost=234.28..1274.33 rows=10012 width=8) (actual time=0.599..6.632 rows=10002 loops=1) Recheck Cond: (ee_er_id = 100) Filter: ((ee_sys_status)::text <> 'D'::text)
33.	0.559	0.559	↑ 1.0	10002	1	→ Bitmap Index Scan on ft_ee_u1 (cost=0.00..231.78 rows=10070 width=0) (actual time=0.559..0.559 rows=10002 loops=1) Index Cond: (ee_er_id = 100)

Sort  (cost=35436.31..35436.48 rows=66 width=734) (actual time=5171.867..5171.869 rows=13 loops=1)

  Sort Key: a.month

  Sort Method: quicksort  Memory: 27kB

  ->  HashAggregate  (cost=35433.66..35434.32 rows=66 width=734) (actual time=5171.845..5171.852 rows=13 loops=1)

        ->  Append  (cost=1405.02..35429.53 rows=66 width=734) (actual time=879.173..5171.793 rows=13 loops=1)

              ->  Merge Right Join  (cost=1405.02..17878.93 rows=65 width=744) (actual time=879.173..2659.361 rows=12 loops=1)

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

                    ->  GroupAggregate  (cost=1403.69..17863.16 rows=1077 width=75) (actual time=879.151..2659.314 rows=9 loops=1)

                          ->  Nested Loop  (cost=1403.69..17225.02 rows=3713 width=75) (actual time=608.500..2385.678 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.232 rows=12 loops=1)

                                      ->  Merge Join  (cost=4.21..4.28 rows=1 width=40) (actual time=0.047..0.105 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.024 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.007..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.033 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.014 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.005 rows=1 loops=12)

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

                                ->  Hash Join  (cost=1399.48..15551.22 rows=35123 width=81) (actual time=8.091..167.737 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=85) (actual time=7.353..118.644 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.784..8.784 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.656..7.136 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.617..0.617 rows=10002 loops=1)

                                                        Index Cond: (ee_er_id = 100)

                    ->  Sort  (cost=1.34..1.37 rows=12 width=4) (actual time=0.018..0.023 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.002..0.004 rows=12 loops=1)

              ->  Aggregate  (cost=17549.67..17549.94 rows=1 width=58) (actual time=2512.425..2512.425 rows=1 loops=1)

                    ->  Nested Loop  (cost=1400.67..17224.79 rows=3713 width=58) (actual time=567.625..2249.249 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.057..0.186 rows=12 loops=1)

                                ->  Hash Join  (cost=1.19..4.05 rows=1 width=40) (actual time=0.048..0.073 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.015..0.023 rows=12 loops=1)

                                            Filter: (taxyear_id = 12)

                                      ->  Hash  (cost=1.18..1.18 rows=1 width=28) (actual time=0.011..0.011 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.006 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.004..0.005 rows=1 loops=12)

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

                          ->  Hash Join  (cost=1399.48..15551.22 rows=35123 width=77) (actual time=2.004..156.272 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=85) (actual time=1.318..105.511 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.173..8.173 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.599..6.632 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.559..0.559 rows=10002 loops=1)

                                                  Index Cond: (ee_er_id = 100)

Per node type stats

node type	count	sum of times	% of query
Aggregate	1	263.176 ms	5.1 %
Append	1	0.007 ms	0.0 %
Bitmap Heap Scan	2	12.592 ms	0.2 %
Bitmap Index Scan	2	1.176 ms	0.0 %
GroupAggregate	1	273.636 ms	5.3 %
Hash	3	3.194 ms	0.1 %
Hash Join	3	1181.330 ms	22.8 %
HashAggregate	1	0.059 ms	0.0 %
Merge Join	1	0.048 ms	0.0 %
Merge Right Join	1	0.024 ms	0.0 %
Nested Loop	4	746.521 ms	14.4 %
Seq Scan	9	2690.037 ms	52.0 %
Sort	4	0.069 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	2689.860 ms	52.0 %
Seq Scan	2	2689.860 ms	100.0 %
ft_employees	2	12.592 ms	0.2 %
Bitmap Heap Scan	2	12.592 ms	100.0 %
ft_sys_tax_weeks	3	0.020 ms	0.0 %
Seq Scan	3	0.020 ms	100.0 %
ft_sys_taxyear	2	0.120 ms	0.0 %
Seq Scan	2	0.120 ms	100.0 %
ft_sys_taxyear_details	2	0.037 ms	0.0 %
Seq Scan	2	0.037 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: eqfc : MODIFIED_P32_V_REPORT

Colorize:

Columns visibility:

Per node type stats

Per table stats