Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id UAA03440 for ; Wed, 5 Jan 2000 20:47:08 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id UAA19743; Wed, 5 Jan 2000 20:47:04 -0500 (EST) To: Jan Wieck cc: Rick Delaney , pgsql-sql@postgreSQL.org Subject: Re: [SQL] Calculation dependencies in views In-reply-to: <3873DD6E.BBBF5813@debis.com> References: <38728CE2.C1756D3E@consumercontact.com> <3872987B.C289AB59@debis.com> <3873A662.37B94F7E@consumercontact.com> <3873BC4C.3D4E4BCC@debis.com> <3873D036.5A2A9B18@consumercontact.com> <3873DD6E.BBBF5813@debis.com> Comments: In-reply-to Jan Wieck message dated "Thu, 06 Jan 2000 01:10:22 +0100" Date: Wed, 05 Jan 2000 20:47:04 -0500 Message-ID: <19740.947123224@sss.pgh.pa.us> From: Tom Lane Jan Wieck 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