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
