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
