#	exclusive	inclusive	rows x	rows	loops	node
1.	514.320	4021.372	↑ 32.6	11	1	→ HashAggregate (cost=2534.17..2609.56 rows=359 width=357) (actual time=4021.208..4021.372 rows=11 loops=1)
2.	45.481	3507.052	↓ 253.5	91020	1	→ Hash Join (cost=9.34..2483.91 rows=359 width=357) (actual time=439.060..3507.052 rows=91020 loops=1) Hash Cond: (fst.taxyear_id = spb.spp_bir_taxyear_id)
3.	27.617	3461.552	↓ 253.5	91020	1	→ Merge Join (cost=8.09..2477.73 rows=359 width=329) (actual time=439.027..3461.552 rows=91020 loops=1) Merge Cond: (sap.sap_taxyear_id = fst.taxyear_id)
4.	0.017	0.030	↓ 1.1	12	1	→ Sort (cost=1.30..1.33 rows=11 width=60) (actual time=0.027..0.030 rows=12 loops=1) Sort Key: sap.sap_taxyear_id Sort Method: quicksort Memory: 25kB
5.	0.013	0.013	↓ 1.1	12	1	→ Seq Scan on ft_sys_sap sap (cost=0.00..1.11 rows=11 width=60) (actual time=0.011..0.013 rows=12 loops=1)
6.	69.872	3433.905	↓ 253.5	91020	1	→ Materialize (cost=6.79..2471.89 rows=359 width=269) (actual time=438.995..3433.905 rows=91020 loops=1)
7.	31.090	3364.033	↓ 253.5	91020	1	→ Merge Join (cost=6.79..2470.99 rows=359 width=269) (actual time=438.989..3364.033 rows=91020 loops=1) Merge Cond: (spa.spp_adp_taxyear_id = fst.taxyear_id)
8.	0.013	0.018	↓ 1.1	12	1	→ Sort (cost=1.30..1.33 rows=11 width=60) (actual time=0.014..0.018 rows=12 loops=1) Sort Key: spa.spp_adp_taxyear_id Sort Method: quicksort Memory: 25kB
9.	0.005	0.005	↓ 1.1	12	1	→ Seq Scan on ft_sys_spp_adp spa (cost=0.00..1.11 rows=11 width=60) (actual time=0.003..0.005 rows=12 loops=1)
10.	77.698	3332.925	↓ 253.5	91020	1	→ Materialize (cost=5.49..2465.15 rows=359 width=209) (actual time=438.969..3332.925 rows=91020 loops=1)
11.	51.886	3255.227	↓ 253.5	91020	1	→ Nested Loop (cost=5.49..2464.25 rows=359 width=209) (actual time=438.967..3255.227 rows=91020 loops=1)
12.	29.909	3203.341	↓ 253.5	91020	1	→ Nested Loop (cost=5.49..2451.49 rows=359 width=207) (actual time=438.941..3203.341 rows=91020 loops=1)
13.	1176.854	2870.228	↓ 104.1	101068	1	→ Nested Loop (cost=5.49..2142.57 rows=971 width=211) (actual time=438.921..2870.228 rows=101068 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)))
14.	0.676	3.822	↓ 144.0	144	1	→ Nested Loop (cost=5.49..42.94 rows=1 width=216) (actual time=0.118..3.822 rows=144 loops=1)
15.	0.652	2.282	↓ 144.0	144	1	→ Nested Loop (cost=5.49..42.66 rows=1 width=198) (actual time=0.104..2.282 rows=144 loops=1) Join Filter: (smp.smp_taxyear_id = fst.taxyear_id)
16.	0.034	0.034	↓ 1.1	12	1	→ Index Scan using ft_sty_p on ft_sys_taxyear fst (cost=0.00..12.42 rows=11 width=38) (actual time=0.011..0.034 rows=12 loops=1)
17.	0.371	1.596	↓ 144.0	144	12	→ Materialize (cost=5.49..30.08 rows=1 width=160) (actual time=0.008..0.133 rows=144 loops=12)
18.	0.224	1.225	↓ 144.0	144	1	→ Nested Loop (cost=5.49..30.07 rows=1 width=160) (actual time=0.090..1.225 rows=144 loops=1) Join Filter: (fstd.month = fstw.month)
19.	0.017	0.017	↓ 12.0	12	1	→ Seq Scan on ft_sys_tax_weeks fstw (cost=0.00..1.18 rows=1 width=28) (actual time=0.005..0.017 rows=12 loops=1) Filter: ((month >= 1) AND (month <= 12))
20.	0.482	0.984	↓ 10.3	144	12	→ Hash Join (cost=5.49..28.72 rows=14 width=136) (actual time=0.009..0.082 rows=144 loops=12) Hash Cond: (aspb.aspp_bir_taxyear_id = smp.smp_taxyear_id)
21.	0.394	0.492	↑ 1.8	144	12	→ Hash Join (cost=4.24..26.38 rows=252 width=76) (actual time=0.007..0.041 rows=144 loops=12) Hash Cond: (aspb.aspp_bir_taxyear_id = fstd.taxyear_id)
22.	0.048	0.048	↑ 29.2	12	12	→ Seq Scan on ft_sys_aspp_bir aspb (cost=0.00..13.50 rows=350 width=60) (actual time=0.002..0.004 rows=12 loops=12)
23.	0.029	0.050	↑ 1.0	144	1	→ Hash (cost=2.44..2.44 rows=144 width=16) (actual time=0.050..0.050 rows=144 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 7kB
24.	0.021	0.021	↑ 1.0	144	1	→ Seq Scan on ft_sys_taxyear_details fstd (cost=0.00..2.44 rows=144 width=16) (actual time=0.008..0.021 rows=144 loops=1)
25.	0.004	0.010	↓ 1.1	12	1	→ Hash (cost=1.11..1.11 rows=11 width=60) (actual time=0.010..0.010 rows=12 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 1kB
26.	0.006	0.006	↓ 1.1	12	1	→ Seq Scan on ft_sys_smp smp (cost=0.00..1.11 rows=11 width=60) (actual time=0.003..0.006 rows=12 loops=1)
27.	0.864	0.864	↑ 1.0	1	144	→ Index Scan using ft_sssp_u1 on ft_sys_ssp ssp (cost=0.00..0.27 rows=1 width=18) (actual time=0.004..0.006 rows=1 loops=144) Index Cond: (ssp_taxyear_id = fst.taxyear_id)
28.	1689.552	1689.552	↑ 1.3	8422	144	→ Index Scan using ft_enetvaly_u on ft_ee_net_values_ytd fenvy (cost=0.00..1538.18 rows=11229 width=71) (actual time=6.035..11.733 rows=8422 loops=144) Index Cond: ((ee_er_id = 100) AND ((process_year)::text = (fst.taxyear)::text))
29.	303.204	303.204	↑ 1.0	1	101068	→ Index Scan using ft_ee_p on ft_employees fee (cost=0.00..0.31 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=101068) Index Cond: (ee_id = fenvy.ee_id) Filter: (((ee_sys_status)::text <> 'D'::text) AND (ee_er_id = 100))
30.	0.000	0.000	↑ 1.0	1	91020	→ Materialize (cost=0.00..8.27 rows=1 width=6) (actual time=0.000..0.000 rows=1 loops=91020)
31.	0.017	0.017	↑ 1.0	1	1	→ Index Scan using ft_eropt_p on ft_er_options feo (cost=0.00..8.27 rows=1 width=6) (actual time=0.017..0.017 rows=1 loops=1) Index Cond: (er_id = 100)
32.	0.005	0.019	↓ 1.1	12	1	→ Hash (cost=1.11..1.11 rows=11 width=60) (actual time=0.019..0.019 rows=12 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 1kB
33.	0.014	0.014	↓ 1.1	12	1	→ Seq Scan on ft_sys_spp_bir spb (cost=0.00..1.11 rows=11 width=60) (actual time=0.010..0.014 rows=12 loops=1)

HashAggregate  (cost=2534.17..2609.56 rows=359 width=357) (actual time=4021.208..4021.372 rows=11 loops=1)

  ->  Hash Join  (cost=9.34..2483.91 rows=359 width=357) (actual time=439.060..3507.052 rows=91020 loops=1)

        Hash Cond: (fst.taxyear_id = spb.spp_bir_taxyear_id)

        ->  Merge Join  (cost=8.09..2477.73 rows=359 width=329) (actual time=439.027..3461.552 rows=91020 loops=1)

              Merge Cond: (sap.sap_taxyear_id = fst.taxyear_id)

              ->  Sort  (cost=1.30..1.33 rows=11 width=60) (actual time=0.027..0.030 rows=12 loops=1)

                    Sort Key: sap.sap_taxyear_id

                    Sort Method: quicksort  Memory: 25kB

                    ->  Seq Scan on ft_sys_sap sap  (cost=0.00..1.11 rows=11 width=60) (actual time=0.011..0.013 rows=12 loops=1)

              ->  Materialize  (cost=6.79..2471.89 rows=359 width=269) (actual time=438.995..3433.905 rows=91020 loops=1)

                    ->  Merge Join  (cost=6.79..2470.99 rows=359 width=269) (actual time=438.989..3364.033 rows=91020 loops=1)

                          Merge Cond: (spa.spp_adp_taxyear_id = fst.taxyear_id)

                          ->  Sort  (cost=1.30..1.33 rows=11 width=60) (actual time=0.014..0.018 rows=12 loops=1)

                                Sort Key: spa.spp_adp_taxyear_id

                                Sort Method: quicksort  Memory: 25kB

                                ->  Seq Scan on ft_sys_spp_adp spa  (cost=0.00..1.11 rows=11 width=60) (actual time=0.003..0.005 rows=12 loops=1)

                          ->  Materialize  (cost=5.49..2465.15 rows=359 width=209) (actual time=438.969..3332.925 rows=91020 loops=1)

                                ->  Nested Loop  (cost=5.49..2464.25 rows=359 width=209) (actual time=438.967..3255.227 rows=91020 loops=1)

                                      ->  Nested Loop  (cost=5.49..2451.49 rows=359 width=207) (actual time=438.941..3203.341 rows=91020 loops=1)

                                            ->  Nested Loop  (cost=5.49..2142.57 rows=971 width=211) (actual time=438.921..2870.228 rows=101068 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=5.49..42.94 rows=1 width=216) (actual time=0.118..3.822 rows=144 loops=1)

                                                        ->  Nested Loop  (cost=5.49..42.66 rows=1 width=198) (actual time=0.104..2.282 rows=144 loops=1)

                                                              Join Filter: (smp.smp_taxyear_id = fst.taxyear_id)

                                                              ->  Index Scan using ft_sty_p on ft_sys_taxyear fst  (cost=0.00..12.42 rows=11 width=38) (actual time=0.011..0.034 rows=12 loops=1)

                                                              ->  Materialize  (cost=5.49..30.08 rows=1 width=160) (actual time=0.008..0.133 rows=144 loops=12)

                                                                    ->  Nested Loop  (cost=5.49..30.07 rows=1 width=160) (actual time=0.090..1.225 rows=144 loops=1)

                                                                          Join Filter: (fstd.month = fstw.month)

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

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

                                                                          ->  Hash Join  (cost=5.49..28.72 rows=14 width=136) (actual time=0.009..0.082 rows=144 loops=12)

                                                                                Hash Cond: (aspb.aspp_bir_taxyear_id = smp.smp_taxyear_id)

                                                                                ->  Hash Join  (cost=4.24..26.38 rows=252 width=76) (actual time=0.007..0.041 rows=144 loops=12)

                                                                                      Hash Cond: (aspb.aspp_bir_taxyear_id = fstd.taxyear_id)

                                                                                      ->  Seq Scan on ft_sys_aspp_bir aspb  (cost=0.00..13.50 rows=350 width=60) (actual time=0.002..0.004 rows=12 loops=12)

                                                                                      ->  Hash  (cost=2.44..2.44 rows=144 width=16) (actual time=0.050..0.050 rows=144 loops=1)

                                                                                            Buckets: 1024  Batches: 1  Memory Usage: 7kB

                                                                                            ->  Seq Scan on ft_sys_taxyear_details fstd  (cost=0.00..2.44 rows=144 width=16) (actual time=0.008..0.021 rows=144 loops=1)

                                                                                ->  Hash  (cost=1.11..1.11 rows=11 width=60) (actual time=0.010..0.010 rows=12 loops=1)

                                                                                      Buckets: 1024  Batches: 1  Memory Usage: 1kB

                                                                                      ->  Seq Scan on ft_sys_smp smp  (cost=0.00..1.11 rows=11 width=60) (actual time=0.003..0.006 rows=12 loops=1)

                                                        ->  Index Scan using ft_sssp_u1 on ft_sys_ssp ssp  (cost=0.00..0.27 rows=1 width=18) (actual time=0.004..0.006 rows=1 loops=144)

                                                              Index Cond: (ssp_taxyear_id = fst.taxyear_id)

                                                  ->  Index Scan using ft_enetvaly_u on ft_ee_net_values_ytd fenvy  (cost=0.00..1538.18 rows=11229 width=71) (actual time=6.035..11.733 rows=8422 loops=144)

                                                        Index Cond: ((ee_er_id = 100) AND ((process_year)::text = (fst.taxyear)::text))

                                            ->  Index Scan using ft_ee_p on ft_employees fee  (cost=0.00..0.31 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=101068)

                                                  Index Cond: (ee_id = fenvy.ee_id)

                                                  Filter: (((ee_sys_status)::text <> 'D'::text) AND (ee_er_id = 100))

                                      ->  Materialize  (cost=0.00..8.27 rows=1 width=6) (actual time=0.000..0.000 rows=1 loops=91020)

                                            ->  Index Scan using ft_eropt_p on ft_er_options feo  (cost=0.00..8.27 rows=1 width=6) (actual time=0.017..0.017 rows=1 loops=1)

                                                  Index Cond: (er_id = 100)

        ->  Hash  (cost=1.11..1.11 rows=11 width=60) (actual time=0.019..0.019 rows=12 loops=1)

              Buckets: 1024  Batches: 1  Memory Usage: 1kB

              ->  Seq Scan on ft_sys_spp_bir spb  (cost=0.00..1.11 rows=11 width=60) (actual time=0.010..0.014 rows=12 loops=1)

Per node type stats

node type	count	sum of times	% of query
Hash	3	0.038 ms	0.0 %
Hash Join	3	46.357 ms	1.2 %
HashAggregate	1	514.320 ms	12.8 %
Index Scan	5	1993.671 ms	49.6 %
Materialize	4	147.941 ms	3.7 %
Merge Join	2	58.707 ms	1.5 %
Nested Loop	6	1260.201 ms	31.3 %
Seq Scan	7	0.124 ms	0.0 %
Sort	2	0.030 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	1689.552 ms	42.0 %
Index Scan	1	1689.552 ms	100.0 %
ft_employees	1	303.204 ms	7.5 %
Index Scan	1	303.204 ms	100.0 %
ft_er_options	1	0.017 ms	0.0 %
Index Scan	1	0.017 ms	100.0 %
ft_sys_aspp_bir	1	0.048 ms	0.0 %
Seq Scan	1	0.048 ms	100.0 %
ft_sys_sap	1	0.013 ms	0.0 %
Seq Scan	1	0.013 ms	100.0 %
ft_sys_smp	1	0.006 ms	0.0 %
Seq Scan	1	0.006 ms	100.0 %
ft_sys_spp_adp	1	0.005 ms	0.0 %
Seq Scan	1	0.005 ms	100.0 %
ft_sys_spp_bir	1	0.014 ms	0.0 %
Seq Scan	1	0.014 ms	100.0 %
ft_sys_ssp	1	0.864 ms	0.0 %
Index Scan	1	0.864 ms	100.0 %
ft_sys_tax_weeks	1	0.017 ms	0.0 %
Seq Scan	1	0.017 ms	100.0 %
ft_sys_taxyear	1	0.034 ms	0.0 %
Index Scan	1	0.034 ms	100.0 %
ft_sys_taxyear_details	1	0.021 ms	0.0 %
Seq Scan	1	0.021 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: Ftd : NEW_SPAY_V_SELECT

Colorize:

Columns visibility:

Per node type stats

Per table stats