Help me in reducing the CPU cost for the high cost query below, asit is hitting production seriously!! - Mailing list pgsql-performance
From | pavan95 |
---|---|
Subject | Help me in reducing the CPU cost for the high cost query below, asit is hitting production seriously!! |
Date | |
Msg-id | 1526883356469-0.post@n3.nabble.com Whole thread Raw |
Responses |
Re: Help me in reducing the CPU cost for the high cost query below,as it is hitting production seriously!!
Re: Help me in reducing the CPU cost for the high cost query below,as it is hitting production seriously!! |
List | pgsql-performance |
Hi all, Hope my mail finds you in good time. I had a problem with a query which is hitting the production seriously. The below is the sub part of the query for which I cannot reduce the CPU cost. Please check and verify whether I'm doing wrong or whether that type index type suits it or not. Kindly help me resolve this issue. *Query*: explain select sum(CASE WHEN MOD(cast(effort_hours as decimal),1) = 0.45 THEN cast(effort_hours as int)+0.75 ELSE CASE WHEN MOD(cast(effort_hours as decimal),1) = 0.15 THEN cast(effort_hours as int) + 0.25 ELSE CASE WHEN MOD(cast(effort_hours as decimal),1) = 0.30 THEN cast(effort_hours as int) + 0.5 ELSE CASE WHEN MOD(cast(effort_hours as decimal),1) = 0 THEN cast(effort_hours as int) end END END END) from tms_timesheet_details, tms_wsr_header header where wsr_header_id=header.id and work_order_no != 'CORPORATE'; QUERY PLAN --------------------------------------------------------------------------------------------- Aggregate (cost=9868.91..9868.92 rows=1 width=8) -> Hash Join (cost=608.27..5647.67 rows=70354 width=8) Hash Cond: (tms_timesheet_details.wsr_header_id = header.id) -> Seq Scan on tms_timesheet_details (cost=0.00..3431.14 rows=72378 width=12) Filter: ((work_order_no)::text <> 'CORPORATE'::text) -> Hash (cost=399.23..399.23 rows=16723 width=4) -> Seq Scan on tms_wsr_header header (cost=0.00..399.23 rows=16723 width=4) (7 rows) The count of number of rows in the tables used are: 1) tms_timesheet_details: amp_test=# select count(*) from tms_timesheet_details; count -------- 110411 (1 row) 2) tms_wsr_header: amp_test=# select count(*) from tms_wsr_header; count ------- 16723 (1 row) The details of the tables and the columns used are as below: 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) "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) Foreign-key constraints: "tms_timesheet_details_create_uid_fkey" FOREIGN KEY (create_uid) REFERENCES res_users(id) ON DELETE SET NULL "tms_timesheet_details_timesheet_header_id_fkey" FOREIGN KEY (timesheet_header_id) REFERENCES tms_timesheet_header(id) ON DELETE SET NULL "tms_timesheet_details_user_id_fkey" FOREIGN KEY (user_id) REFERENCES res_users(id) ON DELETE SET NULL "tms_timesheet_details_write_uid_fkey" FOREIGN KEY (write_uid) REFERENCES res_users(id) ON DELETE SET NULL "tms_timesheet_details_wsr_header_id_fkey" FOREIGN KEY (wsr_header_id) REFERENCES tms_wsr_header(id) ON DELETE SET NULL 2) tms_wsr_header: amp_test=# \d tms_wsr_header Table "public.tms_wsr_header" Column | Type | Modifiers ---------------------+-----------------------------+------------------------------------------------------------- id | integer | not null default nextval('tms_wsr_header_id_seq'::regclass) create_uid | integer | status_id | integer | ao_emp_name | character varying | ao_emp_id | character varying | res_employee_id | character varying | comments | text | write_uid | integer | write_date | timestamp without time zone | create_date | timestamp without time zone | timesheet_period_id | integer | user_id | integer | Indexes: "tms_wsr_header_pkey" PRIMARY KEY, btree (id) "res_employee_idx" btree (res_employee_id) "tmesheet_perd_idx" btree (timesheet_period_id) Foreign-key constraints: "tms_wsr_header_create_uid_fkey" FOREIGN KEY (create_uid) REFERENCES res_users(id) ON DELETE SET NULL "tms_wsr_header_status_id_fkey" FOREIGN KEY (status_id) REFERENCES tms_timesheet_status(id) ON DELETE SET NULL "tms_wsr_header_timesheet_period_id_fkey" FOREIGN KEY (timesheet_period_id) REFERENCES tms_timesheet_period(id) ON DELETE SET NULL "tms_wsr_header_user_id_fkey" FOREIGN KEY (user_id) REFERENCES res_users(id) ON DELETE SET NULL "tms_wsr_header_write_uid_fkey" FOREIGN KEY (write_uid) REFERENCES res_users(id) ON DELETE SET NULL Referenced by: TABLE "tms_release_allocation_comments" CONSTRAINT "tms_release_allocation_comments_wsr_header_id_fkey" FOREIGN KEY (wsr_header_id) REFERENCES tms_wsr_header(id) ON DELETE SET NULL TABLE "tms_timesheet_details" CONSTRAINT "tms_timesheet_details_wsr_header_id_fkey" FOREIGN KEY (wsr_header_id) REFERENCES tms_wsr_header(id) ON DELETE SET NULL TABLE "tms_workflow_history" CONSTRAINT "tms_workflow_history_wsr_id_fkey" FOREIGN KEY (wsr_id) REFERENCES tms_wsr_header(id) ON DELETE SET NULL Hope the above information is sufficient. Kindly show me a way to reduce the cost of this query ASAP. Thanks in advance. Regards, Pavan -- Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html
pgsql-performance by date: