#	exclusive	inclusive	rows x	rows	loops	node
1.	1269.291	3741.819	↑ 1.7	26651	1	→ Merge Left Join (cost=94078.36..109791.24 rows=46543 width=16863) (actual time=2087.679..3741.819 rows=26651 loops=1) Merge Cond: (pp.id = pstd.project_id)
2.	137.327	2027.393	↑ 1.0	26651	1	→ Merge Left Join (cost=86806.00..87791.21 rows=26651 width=16771) (actual time=1688.630..2027.393 rows=26651 loops=1) Merge Cond: (p.id = pfwa.id)
3.	113.850	1244.956	↑ 1.0	26651	1	→ Merge Left Join (cost=44897.51..45248.64 rows=26651 width=16763) (actual time=1086.736..1244.956 rows=26651 loops=1) Merge Cond: (p.id = ca.id)
4.	124.265	981.071	↑ 1.0	26651	1	→ Sort (cost=18289.86..18356.49 rows=26651 width=16755) (actual time=939.584..981.071 rows=26651 loops=1) Sort Key: p.id Sort Method: quicksort Memory: 16919kB
5.	97.581	856.806	↑ 1.0	26651	1	→ Hash Left Join (cost=2736.96..16330.76 rows=26651 width=16755) (actual time=170.517..856.806 rows=26651 loops=1) Hash Cond: ((p.primary_office_id)::numeric = cmt_lut.office_id)
6.	85.376	752.517	↑ 1.0	26651	1	→ Hash Left Join (cost=2515.59..15643.00 rows=26651 width=16698) (actual time=163.780..752.517 rows=26651 loops=1) Hash Cond: (p.fws_poc_id = ecos_data.person.person_id)
7.	77.526	649.420	↑ 1.0	26651	1	→ Hash Left Join (cost=2221.03..14848.77 rows=26651 width=16685) (actual time=146.038..649.420 rows=26651 loops=1) Hash Cond: (p.created_by = ecos_data.person.person_id)
8.	78.525	554.134	↑ 1.0	26651	1	→ Hash Left Join (cost=1926.47..14271.25 rows=26651 width=16672) (actual time=128.262..554.134 rows=26651 loops=1) Hash Cond: (p.last_updated_by = ecos_data.person.person_id)
9.	78.472	457.175	↑ 1.0	26651	1	→ Hash Left Join (cost=1631.91..13678.19 rows=26651 width=16659) (actual time=109.815..457.175 rows=26651 loops=1) Hash Cond: (p.project_status_id = pstat.id)
10.	82.469	378.681	↑ 1.0	26651	1	→ Hash Left Join (cost=1630.82..13314.52 rows=26651 width=16441) (actual time=109.776..378.681 rows=26651 loops=1) Hash Cond: (p.id = sptl.project_id)
11.	123.893	273.120	↑ 1.0	26651	1	→ Hash Join (cost=1160.66..12479.16 rows=26651 width=16433) (actual time=86.665..273.120 rows=26651 loops=1) Hash Cond: (p.id = pp.id)
12.	62.612	62.612	↑ 2.0	30094	1	→ Seq Scan on project p (cost=0.00..10374.88 rows=60188 width=16321) (actual time=0.018..62.612 rows=30094 loops=1)
13.	42.889	86.615	↑ 1.0	26651	1	→ Hash (cost=827.52..827.52 rows=26651 width=112) (actual time=86.615..86.615 rows=26651 loops=1)
14.	43.726	43.726	↑ 1.0	26651	1	→ Seq Scan on project_point pp (cost=0.00..827.52 rows=26651 width=112) (actual time=0.175..43.726 rows=26651 loops=1) Filter: (geom IS NOT NULL)
15.	11.807	23.092	↑ 2.0	7442	1	→ Hash (cost=285.63..285.63 rows=14763 width=12) (actual time=23.092..23.092 rows=7442 loops=1)
16.	11.285	11.285	↑ 2.0	7442	1	→ Seq Scan on rprt_project_sptl_data sptl (cost=0.00..285.63 rows=14763 width=12) (actual time=0.164..11.285 rows=7442 loops=1)
17.	0.012	0.022	↑ 1.0	4	1	→ Hash (cost=1.04..1.04 rows=4 width=222) (actual time=0.022..0.022 rows=4 loops=1)
18.	0.010	0.010	↑ 1.0	4	1	→ Seq Scan on project_status_lut pstat (cost=0.00..1.04 rows=4 width=222) (actual time=0.006..0.010 rows=4 loops=1)
19.	9.415	18.434	↓ 1.0	5707	1	→ Hash (cost=226.47..226.47 rows=5447 width=17) (actual time=18.434..18.434 rows=5707 loops=1)
20.	9.019	9.019	↓ 1.0	5707	1	→ Seq Scan on person (cost=0.00..226.47 rows=5447 width=17) (actual time=0.011..9.019 rows=5707 loops=1)
21.	9.354	17.760	↓ 1.0	5707	1	→ Hash (cost=226.47..226.47 rows=5447 width=17) (actual time=17.760..17.760 rows=5707 loops=1)
22.	8.406	8.406	↓ 1.0	5707	1	→ Seq Scan on person (cost=0.00..226.47 rows=5447 width=17) (actual time=0.005..8.406 rows=5707 loops=1)
23.	9.294	17.721	↓ 1.0	5707	1	→ Hash (cost=226.47..226.47 rows=5447 width=17) (actual time=17.721..17.721 rows=5707 loops=1)
24.	8.427	8.427	↓ 1.0	5707	1	→ Seq Scan on person (cost=0.00..226.47 rows=5447 width=17) (actual time=0.005..8.427 rows=5707 loops=1)
25.	2.996	6.708	↓ 1.0	1663	1	→ Hash (cost=200.61..200.61 rows=1661 width=65) (actual time=6.708..6.708 rows=1663 loops=1)
26.	3.712	3.712	↓ 1.0	1663	1	→ Seq Scan on cmt_lut (cost=0.00..200.61 rows=1661 width=65) (actual time=0.009..3.712 rows=1663 loops=1)
27.	6.060	150.035	↑ 12.8	1805	1	→ Sort (cost=26607.64..26665.42 rows=23110 width=12) (actual time=147.134..150.035 rows=1805 loops=1) Sort Key: ca.id Sort Method: quicksort Memory: 133kB
28.	4.707	143.975	↑ 12.8	1805	1	→ Subquery Scan ca (cost=24412.63..24932.60 rows=23110 width=12) (actual time=136.634..143.975 rows=1805 loops=1)
29.	8.434	139.268	↑ 12.8	1805	1	→ HashAggregate (cost=24412.63..24701.50 rows=23110 width=12) (actual time=136.629..139.268 rows=1805 loops=1)
30.	10.362	130.834	↑ 7.0	3311	1	→ Hash Join (cost=11127.23..24297.08 rows=23110 width=12) (actual time=113.127..130.834 rows=3311 loops=1) Hash Cond: (ca.project_id = p.id)
31.	7.763	7.763	↑ 7.0	3311	1	→ Index Scan using prtype_idx on accomplishment ca (cost=0.00..12678.76 rows=23110 width=12) (actual time=0.359..7.763 rows=3311 loops=1) Index Cond: (program_type_id = 2)
32.	48.915	112.709	↑ 2.0	30094	1	→ Hash (cost=10374.88..10374.88 rows=60188 width=4) (actual time=112.709..112.709 rows=30094 loops=1)
33.	63.794	63.794	↑ 2.0	30094	1	→ Seq Scan on project p (cost=0.00..10374.88 rows=60188 width=4) (actual time=0.012..63.794 rows=30094 loops=1)
34.	93.960	645.110	↑ 2.2	26923	1	→ Sort (cost=41908.50..42058.97 rows=60188 width=12) (actual time=601.867..645.110 rows=26923 loops=1) Sort Key: pfwa.id Sort Method: quicksort Memory: 2031kB
35.	71.758	551.150	↑ 2.2	26923	1	→ Subquery Scan pfwa (cost=35776.19..37130.42 rows=60188 width=12) (actual time=437.629..551.150 rows=26923 loops=1)
36.	120.878	479.392	↑ 2.2	26923	1	→ HashAggregate (cost=35776.19..36528.54 rows=60188 width=12) (actual time=437.624..479.392 rows=26923 loops=1)
37.	145.935	358.514	↑ 2.4	45263	1	→ Hash Join (cost=11127.23..35235.83 rows=108072 width=12) (actual time=130.791..358.514 rows=45263 loops=1) Hash Cond: (pfwa.project_id = p.id)
38.	98.947	98.947	↑ 7.0	45263	1	→ Seq Scan on accomplishment pfwa (cost=0.00..19466.01 rows=316610 width=12) (actual time=17.095..98.947 rows=45263 loops=1) Filter: (program_type_id = 1)
39.	49.001	113.632	↑ 2.0	30094	1	→ Hash (cost=10374.88..10374.88 rows=60188 width=4) (actual time=113.632..113.632 rows=30094 loops=1)
40.	64.631	64.631	↑ 2.0	30094	1	→ Seq Scan on project p (cost=0.00..10374.88 rows=60188 width=4) (actual time=0.010..64.631 rows=30094 loops=1)
41.	99.782	445.135	↑ 1.8	26399	1	→ Sort (cost=7272.36..7388.71 rows=46543 width=100) (actual time=398.744..445.135 rows=26399 loops=1) Sort Key: pstd.project_id Sort Method: quicksort Memory: 2836kB
42.	70.107	345.353	↑ 1.8	26399	1	→ Subquery Scan pstd (cost=2383.88..3663.81 rows=46543 width=100) (actual time=223.016..345.353 rows=26399 loops=1)
43.	207.271	275.246	↑ 1.8	26399	1	→ HashAggregate (cost=2383.88..3198.38 rows=46543 width=32) (actual time=223.012..275.246 rows=26399 loops=1)
44.	67.975	67.975	↑ 2.0	45754	1	→ Seq Scan on rprt_accomp_sptl_calc a (cost=0.00..1471.44 rows=91244 width=32) (actual time=0.704..67.975 rows=45754 loops=1)

"Merge Left Join  (cost=94078.36..109791.24 rows=46543 width=16863) (actual time=2087.679..3741.819 rows=26651 loops=1)"
"  Merge Cond: (pp.id = pstd.project_id)"
"  ->  Merge Left Join  (cost=86806.00..87791.21 rows=26651 width=16771) (actual time=1688.630..2027.393 rows=26651 loops=1)"
"        Merge Cond: (p.id = pfwa.id)"
"        ->  Merge Left Join  (cost=44897.51..45248.64 rows=26651 width=16763) (actual time=1086.736..1244.956 rows=26651 loops=1)"
"              Merge Cond: (p.id = ca.id)"
"              ->  Sort  (cost=18289.86..18356.49 rows=26651 width=16755) (actual time=939.584..981.071 rows=26651 loops=1)"
"                    Sort Key: p.id"
"                    Sort Method:  quicksort  Memory: 16919kB"
"                    ->  Hash Left Join  (cost=2736.96..16330.76 rows=26651 width=16755) (actual time=170.517..856.806 rows=26651 loops=1)"
"                          Hash Cond: ((p.primary_office_id)::numeric = cmt_lut.office_id)"
"                          ->  Hash Left Join  (cost=2515.59..15643.00 rows=26651 width=16698) (actual time=163.780..752.517 rows=26651 loops=1)"
"                                Hash Cond: (p.fws_poc_id = ecos_data.person.person_id)"
"                                ->  Hash Left Join  (cost=2221.03..14848.77 rows=26651 width=16685) (actual time=146.038..649.420 rows=26651 loops=1)"
"                                      Hash Cond: (p.created_by = ecos_data.person.person_id)"
"                                      ->  Hash Left Join  (cost=1926.47..14271.25 rows=26651 width=16672) (actual time=128.262..554.134 rows=26651 loops=1)"
"                                            Hash Cond: (p.last_updated_by = ecos_data.person.person_id)"
"                                            ->  Hash Left Join  (cost=1631.91..13678.19 rows=26651 width=16659) (actual time=109.815..457.175 rows=26651 loops=1)"
"                                                  Hash Cond: (p.project_status_id = pstat.id)"
"                                                  ->  Hash Left Join  (cost=1630.82..13314.52 rows=26651 width=16441) (actual time=109.776..378.681 rows=26651 loops=1)"
"                                                        Hash Cond: (p.id = sptl.project_id)"
"                                                        ->  Hash Join  (cost=1160.66..12479.16 rows=26651 width=16433) (actual time=86.665..273.120 rows=26651 loops=1)"
"                                                              Hash Cond: (p.id = pp.id)"
"                                                              ->  Seq Scan on project p  (cost=0.00..10374.88 rows=60188 width=16321) (actual time=0.018..62.612 rows=30094 loops=1)"
"                                                              ->  Hash  (cost=827.52..827.52 rows=26651 width=112) (actual time=86.615..86.615 rows=26651 loops=1)"
"                                                                    ->  Seq Scan on project_point pp  (cost=0.00..827.52 rows=26651 width=112) (actual time=0.175..43.726 rows=26651 loops=1)"
"                                                                          Filter: (geom IS NOT NULL)"
"                                                        ->  Hash  (cost=285.63..285.63 rows=14763 width=12) (actual time=23.092..23.092 rows=7442 loops=1)"
"                                                              ->  Seq Scan on rprt_project_sptl_data sptl  (cost=0.00..285.63 rows=14763 width=12) (actual time=0.164..11.285 rows=7442 loops=1)"
"                                                  ->  Hash  (cost=1.04..1.04 rows=4 width=222) (actual time=0.022..0.022 rows=4 loops=1)"
"                                                        ->  Seq Scan on project_status_lut pstat  (cost=0.00..1.04 rows=4 width=222) (actual time=0.006..0.010 rows=4 loops=1)"
"                                            ->  Hash  (cost=226.47..226.47 rows=5447 width=17) (actual time=18.434..18.434 rows=5707 loops=1)"
"                                                  ->  Seq Scan on person  (cost=0.00..226.47 rows=5447 width=17) (actual time=0.011..9.019 rows=5707 loops=1)"
"                                      ->  Hash  (cost=226.47..226.47 rows=5447 width=17) (actual time=17.760..17.760 rows=5707 loops=1)"
"                                            ->  Seq Scan on person  (cost=0.00..226.47 rows=5447 width=17) (actual time=0.005..8.406 rows=5707 loops=1)"
"                                ->  Hash  (cost=226.47..226.47 rows=5447 width=17) (actual time=17.721..17.721 rows=5707 loops=1)"
"                                      ->  Seq Scan on person  (cost=0.00..226.47 rows=5447 width=17) (actual time=0.005..8.427 rows=5707 loops=1)"
"                          ->  Hash  (cost=200.61..200.61 rows=1661 width=65) (actual time=6.708..6.708 rows=1663 loops=1)"
"                                ->  Seq Scan on cmt_lut  (cost=0.00..200.61 rows=1661 width=65) (actual time=0.009..3.712 rows=1663 loops=1)"
"              ->  Sort  (cost=26607.64..26665.42 rows=23110 width=12) (actual time=147.134..150.035 rows=1805 loops=1)"
"                    Sort Key: ca.id"
"                    Sort Method:  quicksort  Memory: 133kB"
"                    ->  Subquery Scan ca  (cost=24412.63..24932.60 rows=23110 width=12) (actual time=136.634..143.975 rows=1805 loops=1)"
"                          ->  HashAggregate  (cost=24412.63..24701.50 rows=23110 width=12) (actual time=136.629..139.268 rows=1805 loops=1)"
"                                ->  Hash Join  (cost=11127.23..24297.08 rows=23110 width=12) (actual time=113.127..130.834 rows=3311 loops=1)"
"                                      Hash Cond: (ca.project_id = p.id)"
"                                      ->  Index Scan using prtype_idx on accomplishment ca  (cost=0.00..12678.76 rows=23110 width=12) (actual time=0.359..7.763 rows=3311 loops=1)"
"                                            Index Cond: (program_type_id = 2)"
"                                      ->  Hash  (cost=10374.88..10374.88 rows=60188 width=4) (actual time=112.709..112.709 rows=30094 loops=1)"
"                                            ->  Seq Scan on project p  (cost=0.00..10374.88 rows=60188 width=4) (actual time=0.012..63.794 rows=30094 loops=1)"
"        ->  Sort  (cost=41908.50..42058.97 rows=60188 width=12) (actual time=601.867..645.110 rows=26923 loops=1)"
"              Sort Key: pfwa.id"
"              Sort Method:  quicksort  Memory: 2031kB"
"              ->  Subquery Scan pfwa  (cost=35776.19..37130.42 rows=60188 width=12) (actual time=437.629..551.150 rows=26923 loops=1)"
"                    ->  HashAggregate  (cost=35776.19..36528.54 rows=60188 width=12) (actual time=437.624..479.392 rows=26923 loops=1)"
"                          ->  Hash Join  (cost=11127.23..35235.83 rows=108072 width=12) (actual time=130.791..358.514 rows=45263 loops=1)"
"                                Hash Cond: (pfwa.project_id = p.id)"
"                                ->  Seq Scan on accomplishment pfwa  (cost=0.00..19466.01 rows=316610 width=12) (actual time=17.095..98.947 rows=45263 loops=1)"
"                                      Filter: (program_type_id = 1)"
"                                ->  Hash  (cost=10374.88..10374.88 rows=60188 width=4) (actual time=113.632..113.632 rows=30094 loops=1)"
"                                      ->  Seq Scan on project p  (cost=0.00..10374.88 rows=60188 width=4) (actual time=0.010..64.631 rows=30094 loops=1)"
"  ->  Sort  (cost=7272.36..7388.71 rows=46543 width=100) (actual time=398.744..445.135 rows=26399 loops=1)"
"        Sort Key: pstd.project_id"
"        Sort Method:  quicksort  Memory: 2836kB"
"        ->  Subquery Scan pstd  (cost=2383.88..3663.81 rows=46543 width=100) (actual time=223.016..345.353 rows=26399 loops=1)"
"              ->  HashAggregate  (cost=2383.88..3198.38 rows=46543 width=32) (actual time=223.012..275.246 rows=26399 loops=1)"
"                    ->  Seq Scan on rprt_accomp_sptl_calc a  (cost=0.00..1471.44 rows=91244 width=32) (actual time=0.704..67.975 rows=45754 loops=1)"
"Total runtime: 3804.526 ms"

Per node type stats

node type	count	sum of times	% of query
Hash	9	183.683 ms	4.9 %
Hash Join	3	280.190 ms	7.5 %
Hash Left Join	6	499.949 ms	13.4 %
HashAggregate	3	336.583 ms	9.0 %
Index Scan	1	7.763 ms	0.2 %
Merge Left Join	3	1520.468 ms	40.6 %
Seq Scan	12	442.544 ms	11.8 %
Sort	4	324.067 ms	8.7 %
Subquery Scan ca	1	4.707 ms	0.1 %
Subquery Scan pfwa	1	71.758 ms	1.9 %
Subquery Scan pstd	1	70.107 ms	1.9 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
accomplishment	2	106.710 ms	2.9 %
Index Scan	1	7.763 ms	7.3 %
Seq Scan	1	98.947 ms	92.7 %
cmt_lut	1	3.712 ms	0.1 %
Seq Scan	1	3.712 ms	100.0 %
person	3	25.852 ms	0.7 %
Seq Scan	3	25.852 ms	100.0 %
project	3	191.037 ms	5.1 %
Seq Scan	3	191.037 ms	100.0 %
project_point	1	43.726 ms	1.2 %
Seq Scan	1	43.726 ms	100.0 %
project_status_lut	1	0.010 ms	0.0 %
Seq Scan	1	0.010 ms	100.0 %
rprt_accomp_sptl_calc	1	67.975 ms	1.8 %
Seq Scan	1	67.975 ms	100.0 %
rprt_project_sptl_data	1	11.285 ms	0.3 %
Seq Scan	1	11.285 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: QGK

Colorize:

Columns visibility:

Per node type stats

Per table stats