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