From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jan Wieck <wieck@debis.com>
Cc: Rick Delaney <rick@consumercontact.com>
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] Calculation dependencies in views
Date: Wed, 05 Jan 2000 20:47:04 -0500
Message-ID: <19740.947123224@sss.pgh.pa.us> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, wieck@debis.com, rick@consumercontact.com
Subject: Re: [SQL] Calculation dependencies in views
In-Reply-To: <19740.947123224@sss.pgh.pa.us>
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> <19740.947123224@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox