Can any one please help me in tuning the query?
explain
select *
from (select * from crmentity where deleted = 0 and createdtime between
(now() - interval '6 month') and now() ) as crmentity
inner join (select * from activity where activitytype = 'Emails' and
date_start between (now() - interval '6 month') and now()) as activity on
crmentity.crmid=activity.activityid
inner join emaildetails on emaildetails.emailid = crmentity.crmid
inner join vantage_email_track on
vantage_email_track.mailid=emaildetails.emailid
left join seactivityrel on seactivityrel.activityid = emaildetails.emailid
QUERY
PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=8725.27..17121.20 rows=197 width=581)
-> Nested Loop (cost=8725.27..16805.64 rows=7 width=573)
-> Hash Join (cost=8725.27..10643.08 rows=789 width=292)
Hash Cond: (emaildetails.emailid =
public.activity.activityid)
-> Seq Scan on emaildetails (cost=0.00..1686.95 rows=44595
width=186)
-> Hash (cost=8664.41..8664.41 rows=4869 width=106)
-> Hash Join (cost=5288.61..8664.41 rows=4869
width=106)
Hash Cond: (vantage_email_track.mailid =
public.activity.activityid)
-> Seq Scan on vantage_email_track
(cost=0.00..1324.52 rows=88852 width=12)
-> Hash (cost=4879.22..4879.22 rows=15071
width=94)
-> Bitmap Heap Scan on activity
(cost=392.45..4879.22 rows=15071 width=94)
Recheck Cond: (((activitytype)::text
= 'Emails'::text) AND (date_start >= (now() - '6 mons'::interval)) AND
(date_start <= now()))
-> Bitmap Index Scan on
activity_activitytype_date_start_idx (cost=0.00..388.68 rows=15071 width=0)
Index Cond:
(((activitytype)::text = 'Emails'::text) AND (date_start >= (now() - '6
mons'::interval)) AND (date_start <= now()))
-> Index Scan using crmentity_pkey on crmentity (cost=0.00..7.80
rows=1 width=281)
Index Cond: (public.crmentity.crmid =
public.activity.activityid)
Filter: ((public.crmentity.deleted = 0) AND
(public.crmentity.createdtime <= now()) AND (public.crmentity.createdtime >=
(now() - '6 mons'::interval)))
-> Index Scan using seactivityrel_activityid_idx on seactivityrel
(cost=0.00..39.57 rows=441 width=8)
Index Cond: (seactivityrel.activityid = emaildetails.emailid)
(19 rows)
