Thread: query tuning help
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)
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)
On 06/14/2010 05:41 AM, AI Rumman wrote: > 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 > Can you send us 'explain analyze' too? > -> Seq Scan on emaildetails (cost=0.00..1686.95 rows=44595 width=186) > -> Seq Scan on vantage_email_track (cost=0.00..1324.52 rows=88852 width=12) do you have indexes on emaildetails(emailid) and vantage_email_track(mailid)? -Andy