Re: Reduce Calculations in SELECT

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Josh Kupershmidt <schmiddy(at)gmail(dot)com>
Cc: Carel Combrink <s25291930(at)tuks(dot)co(dot)za>, PostgreSQL Novice List <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Reduce Calculations in SELECT
Date: 2010-08-31 15:01:54
Message-ID: 2940.1283266914@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-novice

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

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Bruce Momjian 2010-08-31 15:46:38 Re: PostgreSQL training
Previous Message Henk van Lingen 2010-08-31 14:50:09 Forcing the right queryplan