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

On Mon, May 21, 2018 at 6:39 AM, pavan95 <pavan(dot)postgresdba(at)gmail(dot)com>
wrote:

> Hi Abbas,
>
> Thanks for your valuable suggestions. To my surprise I got the same output
> as what I have executed before.
>
> But unfortunately I'm unable to understand the logic of the code, in
> specific what is base 60 number? The used data type for "effort_hours"
> column is 'double precision'.
>
> Kindly help me in understanding the logic. Thanks in advance.

This is not converting a "base 60 number to base 10" - this is computing a
percentage, which is indeed what you want to do.

Since 0.60 is the maximum value of the fraction in this encoding scheme
dividing the actual value by 0.60 tells you what percentage (between 0 and
1) your value is of the maximum. But you have to get rid of the hours
component first, and floor truncates the minutes leaving just the hours
which you can subtract out from the original leaving only the minutes.
​
David J.​

P.S. ​You could consider adding a new column to the table, along with a
trigger, and compute and store the derived value upon insert.
​