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

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