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
