#	exclusive	inclusive	rows x	rows	loops	node
1.	174.664	2474.382	↑ 119.7	9	1	→ GroupAggregate (cost=1403.69..17383.39 rows=1077 width=53) (actual time=846.299..2474.382 rows=9 loops=1)
2.	369.772	2299.718	↓ 24.2	90018	1	→ Nested Loop (cost=1403.69..17225.02 rows=3713 width=53) (actual time=596.072..2299.718 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.043	0.214	↓ 12.0	12	1	→ Nested Loop (cost=4.21..5.46 rows=1 width=70) (actual time=0.056..0.214 rows=12 loops=1)
4.	0.049	0.111	↓ 12.0	12	1	→ Merge Join (cost=4.21..4.28 rows=1 width=40) (actual time=0.050..0.111 rows=12 loops=1) Merge Cond: (fstw.month = fstd.month)
5.	0.022	0.032	↓ 12.0	12	1	→ Sort (cost=1.19..1.19 rows=1 width=28) (actual time=0.025..0.032 rows=12 loops=1) Sort Key: fstw.month Sort Method: quicksort Memory: 25kB
6.	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))
7.	0.016	0.030	↑ 1.0	12	1	→ Sort (cost=3.02..3.05 rows=12 width=16) (actual time=0.022..0.030 rows=12 loops=1) Sort Key: fstd.month Sort Method: quicksort Memory: 25kB
8.	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)
9.	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))
10.	501.001	1929.732	↓ 2.6	90018	12	→ Hash Join (cost=1399.48..15551.22 rows=35123 width=65) (actual time=8.079..160.811 rows=90018 loops=12) Hash Cond: (fenvy.ee_id = fee.ee_id)
11.	1419.948	1419.948	↓ 1.1	100066	12	→ Seq Scan on ft_ee_net_values_ytd fenvy (cost=0.00..12377.43 rows=94872 width=69) (actual time=7.342..118.329 rows=100066 loops=12) Filter: (((process_year)::text = '20122013'::text) AND (ee_er_id = 100))
12.	1.638	8.783	↑ 1.0	10002	1	→ Hash (cost=1274.33..1274.33 rows=10012 width=8) (actual time=8.783..8.783 rows=10002 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 391kB
13.	6.524	7.145	↑ 1.0	10002	1	→ Bitmap Heap Scan on ft_employees fee (cost=234.28..1274.33 rows=10012 width=8) (actual time=0.660..7.145 rows=10002 loops=1) Recheck Cond: (ee_er_id = 100) Filter: ((ee_sys_status)::text <> 'D'::text)
14.	0.621	0.621	↑ 1.0	10002	1	→ Bitmap Index Scan on ft_ee_u1 (cost=0.00..231.78 rows=10070 width=0) (actual time=0.621..0.621 rows=10002 loops=1) Index Cond: (ee_er_id = 100)

GroupAggregate  (cost=1403.69..17383.39 rows=1077 width=53) (actual time=846.299..2474.382 rows=9 loops=1)

  ->  Nested Loop  (cost=1403.69..17225.02 rows=3713 width=53) (actual time=596.072..2299.718 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.056..0.214 rows=12 loops=1)

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

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

                    ->  Sort  (cost=1.19..1.19 rows=1 width=28) (actual time=0.025..0.032 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.022..0.030 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=65) (actual time=8.079..160.811 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=69) (actual time=7.342..118.329 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.783..8.783 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.660..7.145 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.621..0.621 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.524 ms	0.3 %
Bitmap Index Scan	1	0.621 ms	0.0 %
GroupAggregate	1	174.664 ms	7.1 %
Hash	1	1.638 ms	0.1 %
Hash Join	1	501.001 ms	20.2 %
Merge Join	1	0.049 ms	0.0 %
Nested Loop	2	369.815 ms	14.9 %
Seq Scan	4	1420.032 ms	57.4 %
Sort	2	0.038 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	1419.948 ms	57.4 %
Seq Scan	1	1419.948 ms	100.0 %
ft_employees	1	6.524 ms	0.3 %
Bitmap Heap Scan	1	6.524 ms	100.0 %
ft_sys_tax_weeks	1	0.010 ms	0.0 %
Seq Scan	1	0.010 ms	100.0 %
ft_sys_taxyear	1	0.060 ms	0.0 %
Seq Scan	1	0.060 ms	100.0 %
ft_sys_taxyear_details	1	0.014 ms	0.0 %
Seq Scan	1	0.014 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: PtAa : NEW_P32_V_REPORT

Colorize:

Columns visibility:

Per node type stats

Per table stats