Re: Reduce Calculations in SELECT

Josh Kupershmidt <schmiddy(at)gmail(dot)com> writes:
> On Tue, Aug 31, 2010 at 10:16 AM, Carel Combrink <s25291930(at)tuks(dot)co(dot)za> wrote:
>> Mod is an expensive operation and it is calculated 3 times for the same set
>> of inputs. How can I get it to only calculate it once and and use the result
>> in the WHERE clause and return the value of the mod?

> Does rewriting as a sub-select like this help:
> SELECT foo.id, foo.mymod
> FROM (SELECT id, mod(one, two) AS mymod FROM mytable) AS foo
> WHERE foo.mymod > 2 AND foo.mymod < 6;

That is the general method for avoiding writing subexpressions multiple
times in SQL. Keep in mind though that unless the function you're
worried about is volatile, the planner will think it's legitimate to
"flatten" the subquery, thus rewriting back to exactly what you had
before. If you're only trying to make the original query more compact,
that may be just fine. If you are really trying to avoid calculating
the function more than once, you may need to stick "OFFSET 0" into the
subquery to act as an optimization fence. (And if you do that, I
strongly advise testing to make sure you're really making things faster
rather than slower...)

regards, tom lane