Thread: Reduce Calculations in SELECT

Hi,

I have a SELECT statement that does the following (example only,
actual query more complex):
SELECT foo.id, mod(foo.one, foo.two)
   FROM my_table AS foo
   WHERE mod(foo.one, foo.two) > 2
       AND mod(foo.one, foo.two) < 6;

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?

For this test my_table looks as follow:
=> SELECT * from my_table;
  id | one | two
----+-----+-----
   1 |   5 |   6
   2 |   1 |   5
   3 |   9 |   3
(3 rows)

Thank you
--
Carel Combrink
s25291930@tuks.co.za

This message and attachments are subject to a disclaimer. Please refer
to www.it.up.ac.za/documentation/governance/disclaimer/ for full
details. / Hierdie boodskap en aanhangsels is aan 'n vrywaringsklousule
onderhewig. Volledige besonderhede is by
www.it.up.ac.za/documentation/governance/disclaimer/ beskikbaar.

On Tue, Aug 31, 2010 at 10:16 AM, Carel Combrink <s25291930@tuks.co.za> wrote:
> Hi,
>
> I have a SELECT statement that does the following (example only, actual
> query more complex):
> SELECT foo.id, mod(foo.one, foo.two)
>  FROM my_table AS foo
>  WHERE mod(foo.one, foo.two) > 2
>      AND mod(foo.one, foo.two) < 6;
>
> 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;

Josh

Josh Kupershmidt <schmiddy@gmail.com> writes:
> On Tue, Aug 31, 2010 at 10:16 AM, Carel Combrink <s25291930@tuks.co.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