Help me in reducing the CPU cost for the high cost query below, asit is hitting production seriously!! - Mailing 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