#	exclusive	inclusive	rows x	rows	loops	node
1.	351.058	669.586	↓ 0.0	0	1	→ Update on wt_student wts (cost=39096.26..59782.89 rows=1 width=825) (actual time=669.586..669.586 rows=0 loops=1)
2.						CTE student_num_info
3.	60.098	60.098	↑ 1.0	22311	1	→ Index Scan using student_fk1 on student (cost=0.00..20658.55 rows=22562 width=16) (actual time=0.017..60.098 rows=22311 loops=1) Index Cond: (partner_id = 231) Filter: ((student_number IS NOT NULL) AND (NOT is_deleted_yn))
4.						CTE single_active
5.	35.199	106.026	↓ 109.8	21950	1	→ HashAggregate (cost=676.86..679.36 rows=200 width=226) (actual time=90.964..106.026 rows=21950 loops=1) Filter: (sum(student_num_info.active_indicator) = 1)
6.	70.827	70.827	↑ 1.0	22311	1	→ CTE Scan on student_num_info (cost=0.00..451.24 rows=22562 width=226) (actual time=0.019..70.827 rows=22311 loops=1)
7.	49.010	318.528	↓ 12785.0	12785	1	→ Hash Join (cost=17758.35..38444.98 rows=1 width=825) (actual time=234.296..318.528 rows=12785 loops=1) Hash Cond: ((s.student_number)::text = (sa.student_number)::text)
8.	38.582	38.582	↑ 1.0	22007	1	→ Index Scan using student_fk1 on student s (cost=0.00..20602.22 rows=22508 width=23) (actual time=0.101..38.582 rows=22007 loops=1) Index Cond: (partner_id = 231) Filter: ((NOT is_deleted_yn) AND ((profile_status_description)::text = 'ACTIVE'::text))
9.	13.525	230.936	↓ 1278.5	12785	1	→ Hash (cost=17758.23..17758.23 rows=10 width=1033) (actual time=230.936..230.936 rows=12785 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 2863kB
10.	24.928	217.411	↓ 1278.5	12785	1	→ Hash Join (cost=17728.63..17758.23 rows=10 width=1033) (actual time=164.540..217.411 rows=12785 loops=1) Hash Cond: ((sa.student_number)::text = (wts.student_id)::text)
11.	118.993	118.993	↓ 109.8	21950	1	→ CTE Scan on single_active sa (cost=0.00..4.00 rows=200 width=460) (actual time=90.990..118.993 rows=21950 loops=1)
12.	12.839	73.490	↑ 1.3	12800	1	→ Hash (cost=17514.89..17514.89 rows=17099 width=573) (actual time=73.490..73.490 rows=12800 loops=1) Buckets: 2048 Batches: 1 Memory Usage: 2217kB
13.	60.651	60.651	↑ 1.3	12800	1	→ Index Scan using wt_student_partner on wt_student wts (cost=0.00..17514.89 rows=17099 width=573) (actual time=0.123..60.651 rows=12800 loops=1) Index Cond: (partner_id = 231) Filter: ((wt_potential_dups_status)::text = 'NO_DUPLICATES'::text)

"Update on wt_student wts  (cost=39096.26..59782.89 rows=1 width=825) (actual time=669.586..669.586 rows=0 loops=1)"
"  CTE student_num_info"
"    ->  Index Scan using student_fk1 on student  (cost=0.00..20658.55 rows=22562 width=16) (actual time=0.017..60.098 rows=22311 loops=1)"
"          Index Cond: (partner_id = 231)"
"          Filter: ((student_number IS NOT NULL) AND (NOT is_deleted_yn))"
"  CTE single_active"
"    ->  HashAggregate  (cost=676.86..679.36 rows=200 width=226) (actual time=90.964..106.026 rows=21950 loops=1)"
"          Filter: (sum(student_num_info.active_indicator) = 1)"
"          ->  CTE Scan on student_num_info  (cost=0.00..451.24 rows=22562 width=226) (actual time=0.019..70.827 rows=22311 loops=1)"
"  ->  Hash Join  (cost=17758.35..38444.98 rows=1 width=825) (actual time=234.296..318.528 rows=12785 loops=1)"
"        Hash Cond: ((s.student_number)::text = (sa.student_number)::text)"
"        ->  Index Scan using student_fk1 on student s  (cost=0.00..20602.22 rows=22508 width=23) (actual time=0.101..38.582 rows=22007 loops=1)"
"              Index Cond: (partner_id = 231)"
"              Filter: ((NOT is_deleted_yn) AND ((profile_status_description)::text = 'ACTIVE'::text))"
"        ->  Hash  (cost=17758.23..17758.23 rows=10 width=1033) (actual time=230.936..230.936 rows=12785 loops=1)"
"              Buckets: 1024  Batches: 1  Memory Usage: 2863kB"
"              ->  Hash Join  (cost=17728.63..17758.23 rows=10 width=1033) (actual time=164.540..217.411 rows=12785 loops=1)"
"                    Hash Cond: ((sa.student_number)::text = (wts.student_id)::text)"
"                    ->  CTE Scan on single_active sa  (cost=0.00..4.00 rows=200 width=460) (actual time=90.990..118.993 rows=21950 loops=1)"
"                    ->  Hash  (cost=17514.89..17514.89 rows=17099 width=573) (actual time=73.490..73.490 rows=12800 loops=1)"
"                          Buckets: 2048  Batches: 1  Memory Usage: 2217kB"
"                          ->  Index Scan using wt_student_partner on wt_student wts  (cost=0.00..17514.89 rows=17099 width=573) (actual time=0.123..60.651 rows=12800 loops=1)"
"                                Index Cond: (partner_id = 231)"
"                                Filter: ((wt_potential_dups_status)::text = 'NO_DUPLICATES'::text)"
"Total runtime: 673.063 ms"

Per node type stats

node type	count	sum of times	% of query
CTE Scan	2	189.820 ms	28.3 %
Hash	2	26.364 ms	3.9 %
Hash Join	2	73.938 ms	11.0 %
HashAggregate	1	35.199 ms	5.3 %
Index Scan	3	159.331 ms	23.8 %
Update	1	351.058 ms	52.4 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
student	2	98.680 ms	14.7 %
Index Scan	2	98.680 ms	100.0 %
wt_student	2	411.709 ms	61.5 %
Index Scan	1	60.651 ms	14.7 %
Update	1	351.058 ms	85.3 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: QZs

Colorize:

Columns visibility:

Per node type stats

Per table stats