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
