Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group