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

Hi all/Justin,

As said, created index on the res_users.res_employee_id and the below link
is the explain plan result.

Link: https://explain.depesz.com/s/hoct <http://> .

And the cost of Previous query is 92,129 and the cost of current modified
query after creating the above said index is 91,462. But good thing is we
can see a very small improvement..!.

Please find the table definitions which are used in the query(which you
asked for tms_worflow_history).

1. tms_timesheet_details:

amp_test=# \d tms_timesheet_details
Table
"public.tms_timesheet_details"
Column | Type |
Modifiers
---------------------+-----------------------------+--------------------------------------------------------------------
id | integer | not null default
nextval('tms_timesheet_details_id_seq'::regclass)
status | character varying |
create_uid | integer |
effort_hours | double precision |
work_order_no | character varying |
res_employee_id | character varying |
wsr_header_id | integer |
remarks | character varying |
write_date | timestamp without time zone |
timesheet_header_id | integer |
date | date |
create_date | timestamp without time zone |
write_uid | integer |
release_no | character varying |
project_id | character varying |
loc_name | character varying |
user_id | integer |
ao_emp_id | character varying |
Indexes:
"tms_timesheet_details_pkey" PRIMARY KEY, btree (id)
"tms_timesheet_details_uniq_res_employee_id_efforts" UNIQUE, btree
(res_employee_id, work_order_no, release_no, date, project_id)
"timesheet_detail_inx" btree (wsr_header_id, timesheet_header_id)
"tms_timesheet_details_all_idx" btree (wsr_header_id, work_order_no,
release_no, date, effort_hours)
"tms_timesheet_details_id_idx" btree (id) WHERE wsr_header_id IS NOT
NULL
"ts_detail_date_idx" btree (date)
"ts_detail_hdr_id_idx" btree (timesheet_header_id)
"ts_detail_release_no_idx" btree (release_no)
"work_order_no_idx" btree (work_order_no)

2. tms_workflow_history:

amp_test=# \d tms_workflow_history
Table "public.tms_workflow_history"
Column | Type |
Modifiers
-------------------+-----------------------------+-------------------------------------------------------------------
id | integer | not null default
nextval('tms_workflow_history_id_seq'::regclass)
create_uid | integer |
current_activity | character varying |
user_id | integer |
sequence | integer |
is_final_approver | boolean |
wsr_id | integer |
write_uid | integer |
timesheet_id | integer |
state | character varying |
write_date | timestamp without time zone |
remarks | character varying |
create_date | timestamp without time zone |
group_id | integer |
active | boolean |
Indexes:
"tms_workflow_history_pkey" PRIMARY KEY, btree (id)
"curract_state_isfinal_app_idx" btree (current_activity, state,
is_final_approver)
"timesheet_id_group_id_active_idx" btree (timesheet_id, group_id,
active)
"tms_wkf_his_active_is_final_approveridx" btree (active,
is_final_approver)
"tms_wkf_his_group_id_idx" btree (group_id)
"tms_wkf_his_timesheet_id_idx" btree (timesheet_id)
"tms_wkf_hist_current_activity_idx" btree (current_activity)
"tms_wkf_hist_state_idx" btree (state)
"wsr_id_idx" btree (wsr_id)

3. res_users:

Table "public.res_users"
Column | Type |
Modifiers
-------------------+-----------------------------+--------------------------------------------------------
id | integer | not null default
nextval('res_users_id_seq'::regclass)
active | boolean | default true
login | character varying | not null
password | character varying |
company_id | integer | not null
partner_id | integer | not null
create_date | timestamp without time zone |
share | boolean |
write_uid | integer |
create_uid | integer |
action_id | integer |
write_date | timestamp without time zone |
signature | text |
password_crypt | character varying |
res_employee_name | character varying |
res_employee_id | character varying |
role | character varying |
skills | character varying |
holiday_header_id | integer |
alias_id | character varying |
loc_name | character varying |
Indexes:
"res_users_pkey" PRIMARY KEY, btree (id)
"res_users_login_key" UNIQUE, btree (login)
"res_users_res_employee_id_idx" btree (res_employee_id)

4. res_partner:

amp_test=# \d res_partner
Table "public.res_partner"
Column | Type |
Modifiers
-------------------------+-----------------------------+----------------------------------------------------------
id | integer | not null default
nextval('res_partner_id_seq'::regclass)
name | character varying |
company_id | integer |
comment | text |
website | character varying |
create_date | timestamp without time zone |
color | integer |
active | boolean |
street | character varying |
supplier | boolean |
city | character varying |
display_name | character varying |
zip | character varying |
title | integer |
country_id | integer |
commercial_company_name | character varying |
parent_id | integer |
company_name | character varying |
employee | boolean |
ref | character varying |
email | character varying |
is_company | boolean |
function | character varying |
lang | character varying |
fax | character varying |
street2 | character varying |
barcode | character varying |
phone | character varying |
write_date | timestamp without time zone |
date | date |
tz | character varying |
write_uid | integer |
customer | boolean |
create_uid | integer |
credit_limit | double precision |
user_id | integer |
mobile | character varying |
type | character varying |
partner_share | boolean |
vat | character varying |
state_id | integer |
commercial_partner_id | integer |
Indexes:
"res_partner_pkey" PRIMARY KEY, btree (id)
"res_partner_commercial_partner_id_index" btree (commercial_partner_id)
"res_partner_company_id_index" btree (company_id)
"res_partner_date_index" btree (date)
"res_partner_display_name_index" btree (display_name)
"res_partner_name_index" btree (name)
"res_partner_parent_id_index" btree (parent_id)
"res_partner_ref_index" btree (ref)
Check constraints:
"res_partner_check_name" CHECK (type::text = 'contact'::text AND name IS
NOT NULL OR type::text <> 'contact'::text)

5. tms_timesheet_status

amp_test=# \d tms_timesheet_status
Table "public.tms_timesheet_status"
Column | Type |
Modifiers
-------------+-----------------------------+-------------------------------------------------------------------
id | integer | not null default
nextval('tms_timesheet_status_id_seq'::regclass)
status | character varying |
create_uid | integer |
description | text |
sequence | integer |
write_uid | integer |
write_date | timestamp without time zone |
create_date | timestamp without time zone |
name | character varying |
Indexes:
"tms_timesheet_status_pkey" PRIMARY KEY, btree (id)

6. tms_timesheet_header:

Table
"public.tms_timesheet_header"
Column | Type |
Modifiers
---------------------+-----------------------------+-------------------------------------------------------------------
id | integer | not null default
nextval('tms_timesheet_header_id_seq'::regclass)
create_uid | integer |
status_id | integer |
ao_emp_name | character varying |
ao_emp_id | character varying |
over | double precision |
res_employee_id | character varying |
regular_pay_hours | double precision |
write_uid | integer |
comments | text |
write_date | timestamp without time zone |
under | double precision |
create_date | timestamp without time zone |
timesheet_period_id | integer |
user_id | integer |
Indexes:
"tms_timesheet_header_pkey" PRIMARY KEY, btree (id)
"tms_timesheet_header_uniq_tms_emp_status" UNIQUE, btree
(res_employee_id, timesheet_period_id)

7. tms_timesheet_period:

Table "public.tms_timesheet_period"
Column | Type |
Modifiers
-------------------+-----------------------------+-------------------------------------------------------------------
id | integer | not null default
nextval('tms_timesheet_period_id_seq'::regclass)
status | character varying |
create_uid | integer |
auto_approve_date | timestamp without time zone |
name | character varying |
end_date | date |
auto_submit_date | timestamp without time zone |
period_type | character varying |
write_date | timestamp without time zone |
payhours | integer |
remarks | text |
create_date | timestamp without time zone |
write_uid | integer |
start_date | date |
Indexes:
"tms_timesheet_period_pkey" PRIMARY KEY, btree (id)

Note: Due to space constraint I'm unable to mention the foreign key
constraints and referenced by for the tables(thinking it is not required)

I have also observed that based on the composite indexes on the columns of
tms_workflow_history table the cost came to 91,462 orelse because of
individual indexes it remains unaltered from 92,129.

I want to reduce the query cost. As observed in the plan a Subquery Scan is
taking around 45000 planner seeks at one place and 38000 planner seeks. Is
there any way to reduce this cost ?

Or any other measures to be followed. My current postgresql version is 9.5.
Thanks in Advance!

Regards,
Pavan

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