Re: [SQL] Calculation dependencies in views

Jan Wieck <wieck(at)debis(dot)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