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

Reduce Calculations in SELECT

From: "Carel Combrink" <s25291930(at)tuks(dot)co(dot)za>
To: "PostgreSQL Novice List" <pgsql-novice(at)postgresql(dot)org>
Subject: Reduce Calculations in SELECT
Date: 2010-08-31 14:16:46
Message-ID: 20100831161646.38933og62kfyft1q@student.up.ac.za (view raw or flat)
Thread:
Lists: pgsql-novice
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(at)tuks(dot)co(dot)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.


Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2010-08-31 14:21:58
Subject: Re: Function Returning Table/Record
Previous:From: SUNDAY A. OLUTAYODate: 2010-08-31 14:12:03
Subject: Re: Django + Postgressql

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