Help needed in Performance Tuning of the query

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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





[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux