How to optimize this query.

select temp.p_id,temp.ds_id, temp.cl_id, count(md_id) from
( select ps.project_id as p_id, sd.merged_defect_id as md_id, min(sd.current_defect_status_id) as ds_id,
case
when count(distinct sd.current_classification_id) = 1
then max(sd.current_classification_id)
else null end as cl_id from project_stream ps
join stream on ps.stream_id = stream.id
join stream_defect sd on sd.stream_id = stream.id group by ps.project_id, sd.merged_defect_id )
as temp
group by temp.p_id, temp.ds_id, temp.cl_id;

----------------------------------------------------------------------------------------------------------------------------
-----------------------------------
HashAggregate (cost=1504839.71..1514243.55 rows=752307 width=32) (actual time=54109.030..54117.451 rows=4029 loops=1)
-> GroupAggregate (cost=1091071.08..1354378.39 rows=7523066 width=32) (actual time=29927.467..50490.362 rows=5021872 lo
ops=1)
-> Sort (cost=1091071.08..1109878.75 rows=7523066 width=32) (actual time=29927.428..33848.621 rows=6226048 loops=
1)
Sort Key: ps.project_id, sd.merged_defect_id
Sort Method: external merge Disk: 255608kB
-> Hash Join (cost=41881.71..51830.26 rows=7523066 width=32) (actual time=1041.006..4893.339 rows=6226048 l
oops=1)
Hash Cond: (ps.stream_id = stream.id)
-> Seq Scan on project_stream ps (cost=0.00..48.06 rows=2106 width=16) (actual time=0.012..1.925 rows
=1994 loops=1)
-> Hash (cost=35074.69..35074.69 rows=544562 width=40) (actual time=1040.665..1040.665 rows=432741 lo
ops=1)
-> Hash Join (cost=53.34..35074.69 rows=544562 width=40) (actual time=0.938..707.705 rows=43274
1 loops=1)
Hash Cond: (sd.stream_id = stream.id)
-> Seq Scan on stream_defect sd (cost=0.00..27533.62 rows=544562 width=32) (actual time=0
.015..133.335 rows=432741 loops=1)
-> Hash (cost=37.04..37.04 rows=1304 width=8) (actual time=0.895..0.895 rows=494 loops=1)
-> Seq Scan on stream (cost=0.00..37.04 rows=1304 width=8) (actual time=0.015..0.52
6 rows=494 loops=1)
Total runtime: 54195.270 ms
(15 rows)