pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jan Wieck <wieck@debis.com>
To: Rick Delaney <rick@consumercontact.com>
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] Calculation dependencies in views
Date: Wed, 05 Jan 2000 02:03:55 +0100
Message-ID: <3872987B.C289AB59@debis.com> (raw)
References: <38728CE2.C1756D3E@consumercontact.com>

Rick Delaney wrote:

> I have a table with, say, a dozen fields and I want to end up with a view with
> around 50 fields, calculated from the original dozen.
>
> So it would be something like this:
>
> CREATE VIEW final AS
>     SELECT
>         x, y, z,
>         (x + y)         as a,
>         (y + z)         as b,
>         (x + y) * z     as c,
>         (y + z) * x     as d,
>         (x + y) * z + x as e,
>         (y + z) * x + x as f
>    FROM my_table;
>
> except my expressions are longer and more complicated.  However, my expressions
> do have similar dependencies and redundancies.

     Be aware that 6.5.* might be unable to deal with the resulting size of
     the rewrite rule. And I'm not sure that I'll get TOAST ready for 7.0 to
     handle it.

> My question is what is a good way of dealing with this?  I was going to do
> something like
>
> CREATE VIEW one AS
>     SELECT
>         id,
>         (x + y)         as a,
>         (y + z)         as b,
>    FROM my_table;
>
> CREATE VIEW two AS
>     SELECT
>         m.id,
>         o.a * m.z     as c,
>         o.b * m.x     as d,
>    FROM my_table m, one o
>    WHERE m.id = o.id;
>
> etc.  but I'll end up with a lot of levels and joins going this route which I
> expect will be pretty slow.

     In the above sample, it will end up in a join, because even after
     rewriting there will be more than one used rangetable entries in the
     query. But if you're able to do the nesting in a manner like this:


          CREATE VIEW one AS
            SELECT
              x, y, z,
              x + y AS sum_xy,
              x + z AS sum_xz,
              y + z AS sum_yz
            FROM my_table;

          CREATE VIEW final AS
            SELECT
              x, y, z,
              sum_xy AS a,
              sum_yz AS b,
              sum_xy * z AS c,
              sum_yz * x AS d,
              sum_xy * z + x AS e,
              sum_yz * x + x AS f
            FROM one;

     and keep all views follow strictly one path

     final -> n-1 -> n-2 -> ... -> two -> one

     you'll end up the a construct that requires nested levels of rewriting.
     But the resulting query will definitely be a single table scan having
     all your complicated expressions attached.

     BTW: if you nest many views, you'll probably get an ERROR about
     "infinite recursion" from the rewriter. In that case, you must raise the
     value of the

     #define REWRITE_INVOKE_MAX 10

     at line 1466 in src/backend/rewrite/rewriteHander.c to some value fairly
     above your nesting level.

> Can someone recommend anything to me?  Should I be approaching this from a
> completely different angle i.e other than views?

     Well, I'm a rewriting- and trigger-man. It really depends on the usual
     behaviour of your data and application.

     Keep in mind, that the above view technique requires each value to be
     computed for each single view attribute for each single row at each
     SELECT. So if you usually SELECT relatively small amounts of your data,
     or update big amounts of it between the SELECT's, it wouldn't hurt.

     But if you're dealing with huge set's of row's at SELECT time and rarely
     changes, you might be better off with setting up a table having all the
     fields in place, and let a trigger put all the calculated values in
     place.


> I'm pretty new at this so any pointers will be appreciated.

     You're welcome.



Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #






view thread (10+ messages)  latest in thread

Message-ID: <3872987B.C289AB59@debis.com>
Permalink:  ../3872987B.C289AB59@debis.com/
Also on:    postgresql.org/message-id/3872987B.C289AB59@debis.com

 · 

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: wieck@debis.com, rick@consumercontact.com
  Subject: Re: [SQL] Calculation dependencies in views
  In-Reply-To: <3872987B.C289AB59@debis.com>
  References: <38728CE2.C1756D3E@consumercontact.com> <3872987B.C289AB59@debis.com>

* 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