Re: [SQL] Calculation dependencies in views

Jan Wieck <wieck@debis.com> writes:
>      Example 1:
>           CREATE VIEW v1 AS
>               SELECT a, a + b AS ab, a + b + c AS abc FROM t1;

>      Example 2:
>           CREATE VIEW v1_sub AS
>               SELECT a, c, a + b AS ab FROM t1;
>           CREATE VIEW v1 AS
>               SELECT a, ab, ab + c AS abc FROM v1_sub;

>      These two examples will result in exactly the same querytree after
>      rewriting, if you SELECT from v1. The second needs two passes in the
>      rewriter, but that's the only difference.

Actually, there's another big difference: the stored rule plan string
for v1 in the second case is shorter than it is in the first case,
because the a+b additions don't show up as operator nodes in v1's
definition in the second case.  (If the references to v1_sub were
flattened out before the rule were stored, it wouldn't take two passes
of rewriting to expand the rule.  But they aren't, and it does ;-).)

I tried these examples and got:

select rulename,length(ev_action) from pg_rewrite where rulename like '_RETv%';
rulename  |length
----------+------
_RETv1    |  1922
_RETv1_sub|  1558
_RETv1_up |  1566
(3 rows)

So, if your problem is that you need to work around the rule plan string
length limit, then indeed eliminating common subexpressions with nested
views can be a win.  There isn't much win in this example, but then we
only got rid of two additions here.
        regards, tom lane