From: Abbas <abbas@rechat.com>
To: pavan95 <pavan.postgresdba@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Help me in reducing the CPU cost for the high cost query below, as it is hitting production seriously!!
Date: Mon, 21 May 2018 15:28:07 +0400
Message-ID: <1526902087.12183.0@homie.mail.dreamhost.com> (raw)
In-Reply-To: <1526901200599-0.post@n3.nabble.com>
References: <1526883356469-0.post@n3.nabble.com>
<1526889830358-0.post@n3.nabble.com>
<1526901200599-0.post@n3.nabble.com>
Hi. Basically you want to convert a base 60 number to a decimal. So you
don't need conditionals. See if this works for you:
SELECT floor(effort_hours) + ( (effort_hours - floor(effort_hours)) /
0.6 )
from tms_timesheet_details detail , tms_wsr_header header where
wsr_header_id=header.id and work_order_no != 'CORPORATE';
Regards,
Abbas
On Mon, May 21, 2018 at 3:43 PM, pavan95 <pavan.postgresdba@gmail.com>
wrote:
> Hi mlunon,
>
> A great thanks for your timely response. And yes it worked when I
> rewritten
> the query.
>
> The query got enhanced with approximate of 1000 planner seeks. You
> can find
> it from the explain plan below:
>
> amp_test=# explain select
> 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
> )
> from tms_timesheet_details detail , tms_wsr_header header where
> wsr_header_id=header.id and work_order_no != 'CORPORATE';
> QUERY PLAN
> ------------------------------------------------------------------------------------------------
> Aggregate (cost=8813.60..8813.61 rows=1 width=8)
> -> Hash Join (cost=608.27..5647.67 rows=70354 width=8)
> Hash Cond: (detail.wsr_header_id = header.id)
> -> Seq Scan on tms_timesheet_details detail
> (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)
>
>
> But is this the optimum, can we reduce the cost more at least to
> around 5000
> planner seeks. As it is only a subpart of the query which is called
> multiple
> number of times in the main query.
>
> And to send the main query along with tables description and explain
> plan it
> will be a vast message so send you a sub-part.
>
> Please help me to tune it more. Thanks in Advance.
>
> Regards,
> Pavan
>
>
>
> --
> Sent from:
> http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: abbas@rechat.com, pavan.postgresdba@gmail.com
Subject: Re: Help me in reducing the CPU cost for the high cost query below, as it is hitting production seriously!!
In-Reply-To: <1526902087.12183.0@homie.mail.dreamhost.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox