Re: Help me in reducing the CPU cost for the high cost query below, as it is hitting production seriously!!

[mlunnon]

pavan95 wrote
> 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';
> 
> 
> 
> --
> Sent from:
> http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html

To start with you can try re-writing this so that it only does the mod cast
once. e.g:
sum ( 
CASE MOD(cast(effort_hours as decimal),1)
WHEN 0.45 THEN cast(effort_hours as int)+0.75
WHEN 0.15 THEN cast(effort_hours as int)+0.25
WHEN 0.30 THEN cast(effort_hours as int)+0.5
WHEN 0 THEN cast(effort_hours as int)
END
)




--
Sent from: 
http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html

Re: Help me in reducing the CPU cost for the high cost query below, as it is hitting production seriously!!

[mlunnon]

pavan95 wrote
> *Query*:
> 
> explain select ... 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)
> 
> 
> --
> Sent from:
> http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html

Why is the table tms_wsr_header in the from clause as it is not used in the
select columns? A simple "wsr_header_id is not null" would do the same as
this is a foreign key into the tms_wsr_header table. An index with on
tms_timesheet_details.id "where wsr_header_id is not null" might then speed
the query up if there were significant numbers of rows with a null
wsr_header_id.
Cheers
Matthew



--
Sent from: 
http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html

Re: Help me in reducing the CPU cost for the high cost query below, as it is hitting production seriously!!

[mlunnon]

Hi Pavan,
that's quite a big query. I can see that the generate_series function is
getting repeatedly called and the planner estimates for this sub query are
out by a factor of 66. You might try to re-write using a WITH query. I am
assuming that you have already analyzed all the tables and also added
appropriate indexes on join/query columns.
Regards
Matthew



--
Sent from: 
http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html