Query optimization with repeated calculations

From: Robert McGehee <rmcgehee(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Query optimization with repeated calculations
Date: 2018-01-19 16:03:48
Message-ID: CAOpVXKoQY6qgHgM9kQbxiTheuTj=zvP-2-Q8+2w9008VsJu8AQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,
I have a general query optimization question involving repeated
calculations.

I frequently want to make views that repeat a calculation over and over
again in lots of columns. In the example below, let’s say it’s (a+b), but
we can imagine the calculation being much more complicated.

For example:
CREATE VIEW AS
SELECT (a+b)*c as c1, (a+b)*d as d1, (a+b)*e as e1
FROM table;

My question is, is PostgreSQL "smart" enough to cache this (a+b)
calculation in the above example, or does it recalculate it in each column?
If it recalculates it in each column, would I generally get better
performance by using a subquery to convince PostgreSQL to cache the
result? For example:

CREATE VIEW AS
SELECT x*c as c1, x*d as d1, x*e as e1
FROM (SELECT (a+b) as x, * FROM table) x;

Or perhaps I could use a CTE? In some instances, I might need 2 or more
subqueries to “cache” all the calculations (if one column depends on
another column that depends on another column), and I feel that sometimes
Postgres makes better optimization decisions when there are not subqueries
involved, but I am not sure.

Obviously I could benchmark everything every single time this comes up, but
there is overhead in that and some kind of rule of thumb or general
guideline would be helpful as it is a frequent question for me.

Thank you!
Robert

Responses

Browse pgsql-general by date

  From Date Subject
Next Message kpi6288 2018-01-19 17:01:51 AW: Use left hand column for null values
Previous Message David G. Johnston 2018-01-19 15:59:40 Re: Use left hand column for null values