PostgreSQL - use previously computed value from same query

Question

I have the following sql query:

SELECT (SELECT ...) AS X, (SELECT ...) AS Y from my_table

'X' is quite hard to compute, and it's used as an input for computing Y. However, if I try to reference X within the query that computes Y or even within the main query I get the following error message:

Error: column "X" does not exist

Is there any way to reference X once it is computed? I really don't want to compute it twice, as this seems very inefficient.

Do you know that it actually gets 'computed' twice?

onedaywhen
– onedaywhen

2011-02-21 16:05:04 +00:00
Commented Feb 21, 2011 at 16:05 — onedaywhen
– onedaywhen, Commented Feb 21, 2011 at 16:05

Rob McDonell · Accepted Answer · 2011-02-22 02:36:29Z

4

PostgreSQL is generally pretty clever in not having to compute the same thing twice. So a query like

SELECT (SELECT hard_to_compute FROM whatever) AS X,
       (SELECT hard_to_compute FROM whatever)*2 AS Y 
FROM my_table

should only need to run the two subqueries once. However if the query is like

SELECT (SELECT hard_to_compute FROM whatever) AS X,
       (SELECT hard_to_compute*2 FROM whatever) AS Y 
FROM my_table

then that might be harder for the optimiser to see what is the same in the two queries.

Another way you can handle this is to create a STABLE function to calculate X. As long as the input is the same, STABLE functions always return the same result within a single statement, so Postgres knows it only needs to run it once. See http://www.postgresql.org/docs/9.0/interactive/xfunc-volatility.html.

edited Feb 22, 2011 at 2:36

answered Feb 22, 2011 at 2:20

Rob McDonell

1,32910 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

OrangeDog Over a year ago

My concern is more for duplicating large complex expressions in code, rather than a failure to plan the query efficiently. Is there any solution to that?

Wayne Conrad · Accepted Answer · 2011-02-21 16:14:37Z

3

A subselect might do it for you, e.g.:

select *, hard_to_compute * 2 as using_hard_to_compute \
from (select *, (i * i) as hard_to_compute from foo) bar;

 i | hard_to_compute | using_hard_to_compute 
---+-----------------+-----------------------
 1 |               1 |                     2
 2 |               4 |                     8
 3 |               9 |                    18

Of course, i * i isn't really hard to compute. I'm just pretending it is for this example.

edited Feb 21, 2011 at 16:14

answered Feb 21, 2011 at 15:38

Wayne Conrad

109k26 gold badges158 silver badges195 bronze badges

Comments

Joe Stefanelli · Accepted Answer · 2011-02-21 15:30:14Z

0

Your best bet might be to run the (SELECT ...) AS X portion of the query first, storing its results in a temporary table. Then use that temporary table in your main query.

answered Feb 21, 2011 at 15:30

Joe Stefanelli

136k21 gold badges244 silver badges242 bronze badges

1 Comment

Greg Smith Over a year ago

Temporary tables are slow and should always be a last resort. This is easy to convert to use a sub-select, which keeps from having to shuffle everything into a temporary area.

Collectives™ on Stack Overflow

PostgreSQL - use previously computed value from same query

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related