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

Re: Reduce Calculations in SELECT

From: Josh Kupershmidt <schmiddy(at)gmail(dot)com>
To: Carel Combrink <s25291930(at)tuks(dot)co(dot)za>
Cc: PostgreSQL Novice List <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Reduce Calculations in SELECT
Date: 2010-08-31 14:48:14
Message-ID: AANLkTinUZEtw-xwLWJU8LQ_4VCVrPvuUb48XHbKQSK=J@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
On Tue, Aug 31, 2010 at 10:16 AM, Carel Combrink <s25291930(at)tuks(dot)co(dot)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

In response to

Responses

pgsql-novice by date

Next:From: Henk van LingenDate: 2010-08-31 14:50:09
Subject: Forcing the right queryplan
Previous:From: Tom LaneDate: 2010-08-31 14:26:50
Subject: Re: Django + Postgressql

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