Re: [SQL] Calculation dependencies in views

Rick Delaney <rick@consumercontact.com> writes:
> I have a table with, say, a dozen fields and I want to end up with a
> view with around 50 fields, calculated from the original dozen.

> So it would be something like this:

> CREATE VIEW final AS
>     SELECT
>         x, y, z,
>         (x + y)         as a,
>         (y + z)         as b,
>         (x + y) * z     as c,
>         (y + z) * x     as d,
>         (x + y) * z + x as e,
>         (y + z) * x + x as f 
>    FROM my_table;

> except my expressions are longer and more complicated.  However, my
> expressions do have similar dependencies and redundancies.

> My question is what is a good way of dealing with this?

I think what you are getting at is avoiding duplicate computations,
such as the repeated evaluations of x + y in the above example?

My guess is that you are wasting your time to worry about it, unless
the repeated calculations are *really* expensive (a float add is down
in the noise ... though a ten-thousand-digit NUMERIC trig function
might not be).

I don't believe that nested views will buy anything given the current
Postgres implementation of views.  As far as I understand it, the
rewriter works by substitution of expressions, so that a reference to
a second-level view will end up being executed just the same as if
you'd written out the expressions in full.  (This will probably change
once we have the much-discussed, little-implemented ability to write
subselects in FROM clauses; but that's a release or two away yet.)

If you are indeed dealing with 10K-digit numerics or something equally
CPU-hoggish, you might consider storing the intermediate results into
a temp table and then doing a second select using the temp table.
But you'd need to be saving an awful lot of calculations to make that
profitable, I think.

If you're just concerned about avoiding code complexity, then by
all means go with the nested views; that will sweep the complicated
expressions under the rug, and someday it will even buy you some
efficiency.  But it's not going to help much if you need to shave
cycles today.
        regards, tom lane