From: Tom Lane <tgl@sss.pgh.pa.us>
To: Rick Delaney <rick@consumercontact.com>
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] Calculation dependencies in views
Date: Wed, 05 Jan 2000 00:35:03 -0500
Message-ID: <16341.947050503@sss.pgh.pa.us> (raw)
In-Reply-To: <38728CE2.C1756D3E@consumercontact.com>
References: <38728CE2.C1756D3E@consumercontact.com>
Rick Delaney <rick@consumercontact.com> writes:
> 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.
> My question is what is a good way of dealing with this?
I think what you are getting at is avoiding duplicate computations,
such as the repeated evaluations of x + y in the above example?
My guess is that you are wasting your time to worry about it, unless
the repeated calculations are *really* expensive (a float add is down
in the noise ... though a ten-thousand-digit NUMERIC trig function
might not be).
I don't believe that nested views will buy anything given the current
Postgres implementation of views. As far as I understand it, the
rewriter works by substitution of expressions, so that a reference to
a second-level view will end up being executed just the same as if
you'd written out the expressions in full. (This will probably change
once we have the much-discussed, little-implemented ability to write
subselects in FROM clauses; but that's a release or two away yet.)
If you are indeed dealing with 10K-digit numerics or something equally
CPU-hoggish, you might consider storing the intermediate results into
a temp table and then doing a second select using the temp table.
But you'd need to be saving an awful lot of calculations to make that
profitable, I think.
If you're just concerned about avoiding code complexity, then by
all means go with the nested views; that will sweep the complicated
expressions under the rug, and someday it will even buy you some
efficiency. But it's not going to help much if you need to shave
cycles today.
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, rick@consumercontact.com
Subject: Re: [SQL] Calculation dependencies in views
In-Reply-To: <16341.947050503@sss.pgh.pa.us>
References: <38728CE2.C1756D3E@consumercontact.com> <16341.947050503@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