Re: [SQL] Calculation dependencies in views

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(at)debis(dot)com (Jan Wieck) #