pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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



view thread (10+ messages)  latest in thread

Message-ID: <19740.947123224@sss.pgh.pa.us>
Permalink:  ../19740.947123224@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/19740.947123224@sss.pgh.pa.us

 · 

reply

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