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