Hi community,
Hope my post finds you in good time. I had the below query which is taking
10 secs to execute the below is the query and its explain plan.
Kindly suggest if I could improve the performance by rewriting the query.
Thanks in advance.
Query:
select
subject,priority,task,company_account,contactname,lead,opportunity,task_assigned,date1,TRIM(status)
,company,activity_type,created_by,comments,call_duration,cmp_id
,login,id,act_type
from
(
select ca.subject as subject,ctp.code as priority,'1' as
task,(case when ca.account_id is not null then cacc.name
else case when ca.opportunity_id is not null then cacc.name
else case when ca.lead_id is not null then cl.company
else case when ca.contact_id is not null then cc.company_name
end
end
end
end) as company_account,cc.name
contactname,cl.first_name||cl.middle_name||cl.last_name
lead,opp.service_offering opportunity,rs.x_name task_assigned,ca.due_date
as date1,cts.name as status,ccp.name as company,ty.name
activity_type,rs.x_name created_by,ca.remarks_text as comments,''
call_duration,ccp.id as cmp_id,rs.login,rs.id,'Tasks' as act_type
from crm_activity ca
left join crm_opportunity opp on(ca.opportunity_id=opp.id)
left join crm_account cacc on (case when ca.account_id is not null then
ca.account_id else opp.account_id end)=cacc.id
left join crm_contacts cc on ca.contact_id =cc.id
left join crm_leads cl on (ca.lead_id=cl.id)
left join res_users rs on(ca.task_assigned_to=rs.id) and
(ca.create_uid=rs.id)
Left join crm_task_status cts on (ca.activity_status_id=cts.id)
Left join crm_task_priorities ctp on(ca.priority_id=ctp.id)
Left join crm_task_types ty on (ca.activity_type_id=ty.id)
Left join crm_companies ccp ON (ca.crm_company_id=ccp.id)
--left join res_users ru on (ca.create_uid=ru.id)
where ca.due_date::date >= '2000-01-01' and ca.due_date::date <=
'2019-12-31'
union all
select ca.name as subject,'' as priority,'0' as task,(case when
ca.account_id is not null then cacc.name
else case when ca.opportunity_id is not null then cacc.name
else case when ca.lead_id is not null then cl.company
else case when ca.contact_id is not null then cc.company_name
end
end
end
end) as company_account,cc.name
contactname,cl.first_name||cl.middle_name||cl.last_name
lead,opp.service_offering opportunity,rs.x_name
task_assigned,ca.start_date as date1,cts.name as status,ccp.name as
company,'Event' activity_type,ru.x_name created_by,ca.description_text as
comments,'' call_duration,ccp.id as cmp_id,rs.login,rs.id,'Events' as
act_type
from crm_events ca
left join crm_opportunity opp on(ca.opportunity_id=opp.id)
left join crm_account cacc on (case when ca.account_id is not null then
ca.account_id else opp.account_id end)=cacc.id
left join crm_contacts cc on ca.contact_id =cc.id
left join crm_leads cl on (ca.lead_id=cl.id)
left join res_users rs on (ca.assigned_to=rs.id)
left join crm_task_status cts on (ca.event_status_id=cts.id)
Left join crm_companies ccp ON (ca.crm_company_id=ccp.id)
Left join res_users ru on (ca.create_uid=ru.id)
where ca.start_date::date >= '2000-01-01' and ca.start_date::date <=
'2019-12-31'
union all
select ca.mail_subject as subject,'' as priority,'0' as
task,(case when ca.account_id is not null then cacc.name
else case when ca.opportunity_id is not null then cacc.name
else case when ca.lead_id is not null then cl.company
else case when ca.contact_id is not null then cc.company_name
end
end
end
end) as company_account,cc.name
contactname,cl.first_name||cl.middle_name||cl.last_name
lead,opp.service_offering opportunity,rs.x_name
task_assigned,ca.create_date as date1,ca.status,ccp.name as company,'Email'
as activity_type,ru.x_name created_by,'' as comments,'' call_duration,ccp.id
as cmp_id,rs.login,rs.id,'Events' as act_type
from crm_email_log ca
left join crm_opportunity opp on(ca.opportunity_id=opp.id)
left join crm_account cacc on (case when ca.account_id is not null then
ca.account_id else opp.account_id end)=cacc.id
left join crm_contacts cc on ca.contact_id =cc.id
left join crm_leads cl on (ca.lead_id=cl.id)
left join res_users rs on(ca.activity_for=rs.id)
Left join crm_companies ccp ON (ca.crm_company_id=ccp.id)
Left join res_users ru on (ca.create_uid=ru.id)
where ca.create_date::date >= '2000-01-01' and ca.create_date::date <=
'2019-12-31'
union all
select ca.subject as subject,'' as priority,'0' as task,(case when
ca.account_id is not null then cacc.name
else case when ca.opportunity_id is not null then cacc.name
else case when ca.lead_id is not null then cl.company
else case when ca.contact_id is not null then cc.company_name
end
end
end
end) as company_account,cc.name
contactname,cl.first_name||cl.middle_name||cl.last_name
lead,opp.service_offering opportunity,rs.x_name task_assigned,ca.create_date
as date1,cts.name status,ccp.name as company,'Call' as
activity_type,ru.x_name created_by,ca.notes_text as comments,''
call_duration,ccp.id as cmp_id,rs.login,rs.id,'Events' as act_type
from crm_call_log ca
left join crm_opportunity opp on(ca.opportunity_id=opp.id)
left join crm_account cacc on (case when ca.account_id is not null then
ca.account_id else opp.account_id end)=cacc.id
left join crm_contacts cc on ca.contact_id =cc.id
left join crm_leads cl on (ca.lead_id=cl.id)
left join res_users rs on(ca.assigned_to=rs.id)
left join crm_task_status cts on (ca.call_log_status_id=cts.id)
Left join crm_companies ccp ON (ca.crm_company_id=ccp.id)
Left join res_users ru on(ca.create_uid=ru.id)
where ca.due_date::date >= '2000-01-01' and ca.due_date::date <=
'2019-12-31'
) as a
where (case when 'Open Activitie' = 'Open Activities' then a.status in
('Yet to Start','Scheduled','In Progress','Open','Not Started') else case
when 'Completed Activities' = 'Completed Activities' then a.status in
('Completed') else 1=1 end end)
and a.status not in ('Cancelled')
and (case when ('Tasks') in ('Tasks') then a.act_type in ('Tasks') else case
when ('Events') in ('Events') then a.act_type in ('Events') else 1=1 end end
)
and (case when 'AL' = 'ALL' then a.company in (select name from
crm_companies ccp) else a.company in ('Ciber NA') end )
--and a.created_by in ('Jay Horowitz','Lee Bingham','Joseph Tocco')
and (case when ('My Activitie') in ('My Activities') then a.login ILIKE
('so-admin')
else case when ('My Team Activitie') in ('My Team Activities') then a.id
in (select id from res_users where crm_manager_id IN (select id from
res_users ru where ru.login ILIKE ('so-admin'))
union
(select id from res_users ru where ru.login ILIKE ('so-admin')))
else
case when ('All Activitie') in ('All Activities') and
(('Sales/BU Management') in (select rg.name from res_groups rg join
ir_module_category irc ON ( rg. category_id=irc.id and irc.name like 'HTC
SalesOffice')
where rg.id in (select gid from res_groups_users_rel
where uid in (select id from res_users ru where ru.login ILIKE
('so-admin')))
and rg.sequence in (select max(sequence) from
res_groups where id in (select gid from res_groups_users_rel where uid in
(select id from res_users ru where ru.login ILIKE ('so-admin')))) ))
then
(a.id in (select id from res_users where crm_manager_id IN
(select id from res_users ru where ru.login ILIKE ('so-admin'))
union
(select id from res_users ru where ru.login ILIKE ('so-admin'))))
else case when ('All Activities') in ('All Activities') and (('Sellers') in
(select rg.name from res_groups rg join ir_module_category irc ON (
rg.category_id=irc.id and irc.name like 'HTC SalesOffice')
where rg.id in (select gid from res_groups_users_rel
where uid in (select id from res_users ru where ru.login ILIKE
('so-admin')))
and rg.sequence in (select max(sequence) from
res_groups where id in (select gid from res_groups_users_rel where uid in
(select id from res_users ru where ru.login ILIKE ('so-admin')))))
) then
(a.login ILIKE ('so-admin'))
else
1=1 end end end end)
and cmp_id in (select cu.company_id from crm_company_users_rel cu inner
join res_users ru on (cu.user_id= ru.id) where ru.login ILIKE
('so-admin'))
order by created_by,activity_type ;
Explain Plan:
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=17645.69..17646.68 rows=396 width=1496)
Sort Key: a.created_by, a.activity_type
-> Hash Semi Join (cost=1142.12..17628.60 rows=396 width=1496)
Hash Cond: (a.cmp_id = cu.company_id)
-> Subquery Scan on a (cost=1110.20..17589.21 rows=792
width=1496)
Filter: CASE WHEN (hashed SubPlan 1) THEN ((a.login)::text
~~* 'so-admin'::text) ELSE true END
-> Append (cost=1039.13..17494.36 rows=1585 width=1496)
-> Result (cost=1039.13..17494.36 rows=1583
width=1496)
-> Append (cost=1039.13..17474.58 rows=1583
width=1492)
-> Gather (cost=1039.13..17458.76
rows=1582 width=792)
Workers Planned: 2
-> Hash Left Join
(cost=39.13..16295.94 rows=659 width=792)
Hash Cond: (ca.activity_type_id
= ty.id)
-> Hash Join
(cost=37.84..16289.09 rows=659 width=683)
Hash Cond:
(ca.crm_company_id = ccp.id)
-> Hash Left Join
(cost=36.75..16270.29 rows=3955 width=651)
Hash Cond:
(ca.priority_id = ctp.id)
-> Hash Left Join
(cost=35.66..16247.91 rows=3955 width=623)
Hash Cond:
((ca.task_assigned_to = rs.id) AND (ca.create_uid = rs.id))
-> Nested
Loop Left Join (cost=2.79..16194.28 rows=3955 width=601)
->
Nested Loop Left Join (cost=2.37..13117.75 rows=3955 width=564)
-> Nested Loop Left Join (cost=1.95..10036.00 rows=3955 width=531)
-> Nested Loop Left Join (cost=1.66..8561.93 rows=3955 width=513)
-> Hash Join (cost=1.24..6001.67 rows=3955 width=475)
Hash Cond: (ca.activity_status_id = cts.id)
-> Parallel Seq Scan on crm_activity ca (cost=0.00..5800.74 rows=59320
width=447)
Filter: ((due_date >= '2000-01-01'::date) AND (due_date <=
'2019-12-31'::date))
-> Hash (cost=1.23..1.23 rows=1 width=36)
-> Seq Scan on crm_task_status cts (cost=0.00..1.23 rows=1 width=36)
Filter: (((name)::text <> 'Cancelled'::text) AND ((name)::text =
'Completed'::text))
-> Index Scan using crm_opportunity_pkey on crm_opportunity opp
(cost=0.42..0.65 rows=1 width=42)
Index Cond: (ca.opportunity_id = id)
-> Index Scan using crm_account_pkey on crm_account cacc (cost=0.29..0.37
rows=1 width=26)
Index Cond: (CASE WHEN (ca.account_id IS NOT NULL) THEN ca.account_id ELSE
opp.account_id END = id)
-> Index Scan using crm_contacts_pkey on crm_contacts cc (cost=0.42..0.78
rows=1 width=37)
Index Cond: (ca.contact_id = id)
->
Index Scan using crm_leads_pkey on crm_leads cl (cost=0.42..0.78 rows=1
width=41)
Index Cond: (ca.lead_id = id)
-> Hash
(cost=23.95..23.95 rows=595 width=30)
-> Seq
Scan on res_users rs (cost=0.00..23.95 rows=595 width=30)
-> Hash
(cost=1.04..1.04 rows=4 width=36)
-> Seq Scan
on crm_task_priorities ctp (cost=0.00..1.04 rows=4 width=36)
-> Hash
(cost=1.07..1.07 rows=1 width=36)
-> Seq Scan on
crm_companies ccp (cost=0.00..1.07 rows=1 width=36)
Filter:
((name)::text = 'Ciber NA'::text)
-> Hash (cost=1.13..1.13
rows=13 width=36)
-> Seq Scan on
crm_task_types ty (cost=0.00..1.13 rows=13 width=36)
-> Result (cost=0.00..0.00 rows=0
width=865)
One-Time Filter: false
-> Result (cost=0.00..0.00 rows=0 width=443)
One-Time Filter: false
-> Result (cost=0.00..0.00 rows=0 width=582)
One-Time Filter: false
SubPlan 1
-> Nested Loop Semi Join (cost=47.26..71.07 rows=1
width=17)
-> Nested Loop (cost=46.70..51.20 rows=1 width=21)
-> Hash Join (cost=46.56..49.99 rows=4
width=25)
Hash Cond: (rg.sequence =
(max(res_groups.sequence)))
-> Seq Scan on res_groups rg
(cost=0.00..3.10 rows=110 width=29)
-> Hash (cost=46.54..46.54 rows=1
width=4)
-> Aggregate (cost=46.52..46.53
rows=1 width=4)
-> Nested Loop
(cost=43.98..46.51 rows=7 width=4)
-> HashAggregate
(cost=43.83..43.90 rows=7 width=4)
Group Key:
res_groups_users_rel_1.gid
-> Nested Loop
(cost=4.34..43.82 rows=7 width=4)
-> Seq
Scan on res_users ru_2 (cost=0.00..25.44 rows=1 width=4)
Filter: ((login)::text ~~* 'so-admin'::text)
-> Bitmap
Heap Scan on res_groups_users_rel res_groups_users_rel_1 (cost=4.34..18.31
rows=7 width=8)
Recheck Cond: (uid = ru_2.id)
->
Bitmap Index Scan on res_groups_users_rel_uid_idx (cost=0.00..4.33 rows=7
width=0)
Index Cond: (uid = ru_2.id)
-> Index Scan using
res_groups_pkey on res_groups (cost=0.14..0.37 rows=1 width=8)
Index Cond: (id =
res_groups_users_rel_1.gid)
-> Index Scan using ir_module_category_pkey on
ir_module_category irc (cost=0.14..0.27 rows=1 width=4)
Index Cond: (id = rg.category_id)
Filter: ((name)::text ~~ 'HTC
SalesOffice'::text)
-> Nested Loop (cost=0.56..19.85 rows=1 width=4)
-> Index Scan using
res_groups_users_rel_gid_idx on res_groups_users_rel (cost=0.28..2.45
rows=55 width=8)
Index Cond: (gid = rg.id)
-> Index Scan using res_users_pkey on
res_users ru_1 (cost=0.28..0.32 rows=1 width=4)
Index Cond: (id =
res_groups_users_rel.uid)
Filter: ((login)::text ~~*
'so-admin'::text)
-> Hash (cost=31.91..31.91 rows=1 width=4)
-> Hash Join (cost=25.45..31.91 rows=1 width=4)
Hash Cond: (cu.user_id = ru.id)
-> Seq Scan on crm_company_users_rel cu
(cost=0.00..5.52 rows=352 width=8)
-> Hash (cost=25.44..25.44 rows=1 width=4)
-> Seq Scan on res_users ru (cost=0.00..25.44
rows=1 width=4)
Filter: ((login)::text ~~*
'so-admin'::text)
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html
