Below is the query I ran for more than 12 hours and yet no result, I had to cancel. Earlier, it used to be completed in less than 10 mins. I've checked the execution plan and looks good too.
Query:
insert into APT_ADHOC_DISH_UNIQ_SRC_AAMIR(email,decile,segment,cass_suite,last_line,file )
select a.email,a.decile,'Green',a.cass_suite,a.last_line,'NEW'
from apt_custdod_dish_trt_u_deciles_aamir a
left join APT_ADHOC_DISH_SRC_TABLE_AAMIR b on a.email=b.email
left join DISH_PREVIOUS_RECORDS c on a.md5hash=c.md5hash
where c.md5hash is null
and b.email is null
order by random() limit 2315108 ;
Query Plan:
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
Insert on apt_adhoc_dish_uniq_src_aamir (cost=31854466.07..31883404.92 rows=2315108 width=229)
-> Subquery Scan on "*SELECT*" (cost=31854466.07..31883404.92 rows=2315108 width=229)
-> Limit (cost=31854466.07..31860253.84 rows=2315108 width=109)
-> Sort (cost=31854466.07..31940398.62 rows=34373023 width=109)
Sort Key: (random())
-> Hash Anti Join (cost=10424.64..21560078.86 rows=34373023 width=109)
Hash Cond: ((a.email)::text = (b.email)::text)
-> Merge Anti Join (cost=522.16..20491390.52 rows=34415656 width=37)
Merge Cond: ((a.md5hash)::text = (c.md5hash)::text)
-> Index Scan using md5_1 on apt_custdod_dish_trt_u_deciles_aamir a (cost=0.57..8465757.97 rows=136067680 width=70)
Filter: ((decile)::text = 'U'::text)
-> Index Only Scan using dish_previous_records_pkey on dish_previous_records c (cost=0.69..10414813.05 rows=101652024 width=33)
-> Hash (cost=6807.55..6807.55 rows=168555 width=23)
-> Seq Scan on apt_adhoc_dish_src_table_aamir b (cost=0.00..6807.55 rows=168555 width=23)
(14 rows)
Note: All columns which are in where clause has index.
ServerInfo
