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) #
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