Re: Help me in reducing the CPU cost for the high cost query below,as it is hitting production seriously!!

Hi all, 
Thank you so much for your valuable responses.Tried every aspect which you
have said for my sub-query.  
I hoped a better decrease in cost for my main query. But yes it decreased
but not to a great extent.
What I felt is to provide the main query and the associated table
definitions in the query. Please help me to tune the following big query. 
select res.id id,
                          row_number() OVER () as sno,
                           res.header_id,
                           res.emp_id,
                           res.alias alias,
                           res.name as name,
                           res.billed_hrs billed_hrs,
                           res.unbilled_hrs unbilled_hrs,
                           res.paid_time_off paid_time_off,
                           res.unpaid_leave unpaid_leave,
                           res.breavement_time breavement_time,
                           res.leave leave,
                           res.state,
                           count(*) OVER() AS full_count,
                           res.header_emp_id,
                           res.header_status
                             from (
            select 
                history.id as id,
                0 as header_id,
                '0' as emp_id,
                 row_number() OVER () as sno,
                user1.alias_id as alias,
                partner.name as name,
                ( select SUM( floor(effort_hours) + ( (effort_hours -
floor(effort_hours)) / 0.6 ))
                    from tms_timesheet_details where timesheet_header_id=header.id and
work_order_no != 'CORPORATE') billed_hrs,
                    
                (select SUM( floor(effort_hours) + ( (effort_hours -
floor(effort_hours)) / 0.6 ))
                    from tms_timesheet_details where timesheet_header_id=header.id and
release_no = 'unbillable_time') as unbilled_hrs,
                (select SUM( floor(effort_hours) + ( (effort_hours -
floor(effort_hours)) / 0.6 ))
                    from tms_timesheet_details where timesheet_header_id=header.id and
release_no = 'paid_time_off') as paid_time_off,
                (select SUM( floor(effort_hours) + ( (effort_hours -
floor(effort_hours)) / 0.6 ))
                    from tms_timesheet_details where timesheet_header_id=header.id and
release_no = 'unpaid_leave') as unpaid_leave,
                (select SUM( floor(effort_hours) + ( (effort_hours -
floor(effort_hours)) / 0.6 ))
                    from tms_timesheet_details where timesheet_header_id=header.id and
release_no = 'bereavement_time') as breavement_time,
                (select SUM( floor(effort_hours) + ( (effort_hours -
floor(effort_hours)) / 0.6 ))
                    from tms_timesheet_details where timesheet_header_id=header.id and date
>='2018-04-16' and date <='2018-04-30' and release_no in
('sick_leave','casual_leave','privilege_leave','optional_holiday') ) as
leave,
                (case when tl_status.state = '' then 'Waiting for approval'
else tl_status.state end) as state,
                header.res_employee_id as header_emp_id,
                status.name as header_status     
                from tms_workflow_history history, 
                    res_users users,
                    res_users user1,
                    res_partner partner,
                    tms_timesheet_status status,
                    tms_timesheet_header header
                    left join tms_workflow_history tl_status on
tl_status.timesheet_id=header.id
                                                             and
tl_status.active=True
                                                             and
tl_status.group_id=13
                    
                where 
                     history.timesheet_id=header.id
                    and header.res_employee_id=user1.res_employee_id
                    and  status.id=header.status_id
                    and history.user_id=users.id
                    and user1.partner_id=partner.id
                    and header.timesheet_period_id = 127
                     and (history.state = 'Approved' )
                    and history.current_activity='N'
                    and history.is_final_approver=True 
                    and history.active = True
   union 
            select 
                0 as id,
                header.id as header_id,
                '0' as emp_id,
                 0 as sno,
                users.alias_id as alias,
                partner.name as name,
                (select SUM( floor(effort_hours) + ( (effort_hours -
floor(effort_hours)) / 0.6 ))
                    from tms_timesheet_details where work_order_no != 'CORPORATE' and
res_employee_id=users.res_employee_id and date in (select date::date from
generate_series((select start_date from tms_timesheet_period where
id=127),(select end_date from tms_timesheet_period where id=127),'1
day'::interval) date)) billed_hrs,
                (select SUM( floor(effort_hours) + ( (effort_hours -
floor(effort_hours)) / 0.6 ))
                    from tms_timesheet_details where release_no = 'unbillable_time' and
res_employee_id=users.res_employee_id and date in (select date::date from
generate_series((select start_date from tms_timesheet_period where
id=127),(select end_date from tms_timesheet_period where id=127),'1
day'::interval) date)) as unbilled_hrs,
                (select SUM( floor(effort_hours) + ( (effort_hours -
floor(effort_hours)) / 0.6 ))
                    from tms_timesheet_details where release_no = 'paid_time_off' and
res_employee_id=users.res_employee_id and date in (select date::date from
generate_series((select start_date from tms_timesheet_period where
id=127),(select end_date from tms_timesheet_period where id=127),'1
day'::interval) date)) as paid_time_off,
                (select SUM( floor(effort_hours) + ( (effort_hours -
floor(effort_hours)) / 0.6 ))
                    from tms_timesheet_details where release_no = 'unpaid_leave' and
res_employee_id=users.res_employee_id and date in (select date::date from
generate_series((select start_date from tms_timesheet_period where
id=127),(select end_date from tms_timesheet_period where id=127),'1
day'::interval) date)) as unpaid_leave,
                (select SUM( floor(effort_hours) + ( (effort_hours -
floor(effort_hours)) / 0.6 ))
                    from tms_timesheet_details where release_no = 'bereavement_time' and
res_employee_id=users.res_employee_id and date in (select date::date from
generate_series((select start_date from tms_timesheet_period where
id=127),(select end_date from tms_timesheet_period where id=127),'1
day'::interval) date)) as breavement_time,
                (select SUM( floor(effort_hours) + ( (effort_hours -
floor(effort_hours)) / 0.6 ))
                    from tms_timesheet_details where res_employee_id=users.res_employee_id
and  date >='2018-04-16' and date <='2018-04-30' and release_no in
('sick_leave','casual_leave','privilege_leave','optional_holiday') ) as
leave,
                'Not Submitted' state,
                header.res_employee_id as header_emp_id,
                'Not Submitted' as header_status     
                from res_users users,
                    res_partner partner,
                    tms_timesheet_status status,
                    tms_timesheet_header header         
                where 
                    header.res_employee_id=users.res_employee_id
                    and  status.id=header.status_id
                    and users.partner_id=partner.id
                    and status.name='Draft'
                    and header.timesheet_period_id=127
                    and header.res_employee_id in (some ids)         
   union    
            select
                0 as id,
                0 as header_id,
                users.res_employee_id as emp_id,
                 0 as sno,
                users.alias_id as alias,
                partner.name as name,
                0 as billed_hrs,
                0 as unbilled_hrs,
                (select SUM( floor(effort_hours) + ( (effort_hours -
floor(effort_hours)) / 0.6 ))
                    from tms_timesheet_details where release_no = 'paid_time_off' and
res_employee_id=users.res_employee_id and date in (select date::date from
generate_series((select start_date from tms_timesheet_period where
id=127),(select end_date from tms_timesheet_period where id=127),'1
day'::interval) date)) as paid_time_off,
                (select SUM( floor(effort_hours) + ( (effort_hours -
floor(effort_hours)) / 0.6 ))
                    from tms_timesheet_details where release_no = 'unpaid_leave' and
res_employee_id=users.res_employee_id and date in (select date::date from
generate_series((select start_date from tms_timesheet_period where
id=127),(select end_date from tms_timesheet_period where id=127),'1
day'::interval) date)) as unpaid_leave,
                (select SUM( floor(effort_hours) + ( (effort_hours -
floor(effort_hours)) / 0.6 ))
                    from tms_timesheet_details where release_no = 'bereavement_time' and
res_employee_id=users.res_employee_id and date in (select date::date from
generate_series((select start_date from tms_timesheet_period where
id=127),(select end_date from tms_timesheet_period where id=127),'1
day'::interval) date)) as breavement_time,
                (select SUM( floor(effort_hours) + ( (effort_hours -
floor(effort_hours)) / 0.6 ))
                    from tms_timesheet_details where res_employee_id=users.res_employee_id
and date >='2018-04-16' and date <='2018-04-30' and release_no in
('sick_leave','casual_leave','privilege_leave','optional_holiday') ) as
leave,
                'Not Submitted' state,
                users.res_employee_id as header_emp_id,
                'Not Submitted' as header_status
                
            from res_users users,
                res_partner partner
            
            where users.res_employee_id not in (select res_employee_id
                                                from 
                                                    tms_timesheet_header
                                                where 
                                                    timesheet_period_id=127
                                                    and res_employee_id in
('A1','B1','C2323',--some 2000 id's))
                and users.partner_id=partner.id
                 and users.res_employee_id is not null
                and users.res_employee_id in ('A1','B1','C2323',--some 2000
id's)
         order by name ) res  order by name limit 10 offset 0

Note: As it is a big query posted only a meaningful part. There 5 unions of
similar type and same are the tables involved in the entire query.

Sample query plan: 
Limit  (cost=92129.35..92129.63 rows=10 width=248)
   ->  WindowAgg  (cost=92129.35..92138.46 rows=331 width=248)
         ->  Subquery Scan on res  (cost=92129.35..92133.49 rows=331
width=248)
               ->  Sort  (cost=92129.35..92130.18 rows=331 width=33)
                     Sort Key: partner.name
                     ->  HashAggregate  (cost=92112.19..92115.50 rows=331
width=33)
                           ->*  Append  (cost=340.02..92099.78 rows=331
width=33)*
                                 ->  WindowAgg  (cost=340.02..1591.76 rows=1
width=54)
                            

(396 rows)
Problem started with append in the plan.

Please help me tune this query!!!!

Thanks in Advance.

Regards,
Pavan




--
Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html