pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Calculation dependencies in views
10+ messages / 3 participants
[nested] [flat]

* Calculation dependencies in views
@ 2000-01-05 00:14 Rick Delaney <rick@consumercontact.com>
  2000-01-05 01:03 ` Re: [SQL] Calculation dependencies in views Jan Wieck <wieck@debis.com>
  2000-01-05 05:35 ` Re: [SQL] Calculation dependencies in views Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 2 replies; 10+ messages in thread

From: Rick Delaney @ 2000-01-05 00:14 UTC (permalink / raw)
  To: pgsql-sql

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 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.

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

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

--Rick



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: [SQL] Calculation dependencies in views
  2000-01-05 00:14 Calculation dependencies in views Rick Delaney <rick@consumercontact.com>
@ 2000-01-05 01:03 ` Jan Wieck <wieck@debis.com>
  2000-01-05 20:15   ` Re: [SQL] Calculation dependencies in views Rick Delaney <rick@consumercontact.com>
  1 sibling, 1 reply; 10+ messages in thread

From: Jan Wieck @ 2000-01-05 01:03 UTC (permalink / raw)
  To: Rick Delaney <rick@consumercontact.com>; +Cc: pgsql-sql

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






^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: [SQL] Calculation dependencies in views
  2000-01-05 00:14 Calculation dependencies in views Rick Delaney <rick@consumercontact.com>
  2000-01-05 01:03 ` Re: [SQL] Calculation dependencies in views Jan Wieck <wieck@debis.com>
@ 2000-01-05 20:15   ` Rick Delaney <rick@consumercontact.com>
  2000-01-05 21:49     ` Re: [SQL] Calculation dependencies in views Jan Wieck <wieck@debis.com>
  0 siblings, 1 reply; 10+ messages in thread

From: Rick Delaney @ 2000-01-05 20:15 UTC (permalink / raw)
  To: Jan Wieck <wieck@debis.com>; +Cc: pgsql-sql

Jan Wieck wrote:
> 
> Rick Delaney wrote:
> 
> >
>      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.
> 
[snip]
>      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.

I'd have to take a good look at all the expressions again to know if I could do
this but I think I probably can.  Only, with what I've started on I'm already
getting "rule plan string too big" errors.  (What I've started on is just one
view that doesn't have all the expressions in it yet).

So I will just get the same thing with nested views, no?  That is how I
understand the "The Postgres Rule System" section of the manual and Tom Lane's
answer to my post.

If I could structure things as

    subset_1_n -> subset_1_n-1 -> ... -> subset_1_2 -> subset_1_1
    subset_2_n -> subset_2_n-1 -> ... -> subset_2_2 -> subset_2_1
    :
    :
    subset_m_n -> subset_m_n-1 -> ... -> subset_m_2 -> subset_m_1

final -> join subset_1_n, subset_2_n, ..., subset_m_n

where each view subset_i_n has a usably-sized rule plan string then could I get
this thing working?

I can (and will) try this out with my stuff but I'm just trying to understand the
general principles.

>      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.

This will be primarily INSERT/UPDATE a row and then SELECT the view of that row. 
Am I right that a trigger would make more sense in this case then?

Thanks a lot for your help,

--Rick



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: [SQL] Calculation dependencies in views
  2000-01-05 00:14 Calculation dependencies in views Rick Delaney <rick@consumercontact.com>
  2000-01-05 01:03 ` Re: [SQL] Calculation dependencies in views Jan Wieck <wieck@debis.com>
  2000-01-05 20:15   ` Re: [SQL] Calculation dependencies in views Rick Delaney <rick@consumercontact.com>
@ 2000-01-05 21:49     ` Jan Wieck <wieck@debis.com>
  2000-01-05 23:13       ` Re: [SQL] Calculation dependencies in views Rick Delaney <rick@consumercontact.com>
  0 siblings, 1 reply; 10+ messages in thread

From: Jan Wieck @ 2000-01-05 21:49 UTC (permalink / raw)
  To: Rick Delaney <rick@consumercontact.com>; +Cc: pgsql-sql

Rick Delaney wrote:

> Jan Wieck wrote:
> >
> > Rick Delaney wrote:
> >
> > >
> >      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.
>
> I'd have to take a good look at all the expressions again to know if I could do
> this but I think I probably can.  Only, with what I've started on I'm already
> getting "rule plan string too big" errors.  (What I've started on is just one
> view that doesn't have all the expressions in it yet).

     That's what I meant with "might be unable...". You talked about a 50
     column view with complicated expressions, and that's surely too large for
     the current implementation.

> So I will just get the same thing with nested views, no?  That is how I
> understand the "The Postgres Rule System" section of the manual and Tom Lane's
> answer to my post.

     As the author of "The Postgres Rule System" I say yes, except that it
     requires some recursions in the rewriter. Thus, it will take a little
     longer to rewrite, but that's a constant time per query, not depending on
     the amount of data you're selecting. So if you're happy with the response
     times for your test queries, it's not the rewriter any more if you get
     response time problems later.

>
> If I could structure things as
>
>     subset_1_n -> subset_1_n-1 -> ... -> subset_1_2 -> subset_1_1
>     subset_2_n -> subset_2_n-1 -> ... -> subset_2_2 -> subset_2_1
>     :
>     :
>     subset_m_n -> subset_m_n-1 -> ... -> subset_m_2 -> subset_m_1
>
> final -> join subset_1_n, subset_2_n, ..., subset_m_n
>
> where each view subset_i_n has a usably-sized rule plan string then could I get
> this thing working?

     As said, there is a limit of 10 recursions for the rewriter actually
     hardcoded in rewriteHandler.c (line 1466). Raise that value if you get
     "infinite loop" errors.

> This will be primarily INSERT/UPDATE a row and then SELECT the view of that row.
> Am I right that a trigger would make more sense in this case then?

     If you only insert one row, then select that one row, the cascaded view
     setup is best IMHO. The trigger solution would only be better, if you
     seldom do INSERT/UPDATE, but often SELECT and maybe SELECT many rows.

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






^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: [SQL] Calculation dependencies in views
  2000-01-05 00:14 Calculation dependencies in views Rick Delaney <rick@consumercontact.com>
  2000-01-05 01:03 ` Re: [SQL] Calculation dependencies in views Jan Wieck <wieck@debis.com>
  2000-01-05 20:15   ` Re: [SQL] Calculation dependencies in views Rick Delaney <rick@consumercontact.com>
  2000-01-05 21:49     ` Re: [SQL] Calculation dependencies in views Jan Wieck <wieck@debis.com>
@ 2000-01-05 23:13       ` Rick Delaney <rick@consumercontact.com>
  2000-01-06 00:10         ` Re: [SQL] Calculation dependencies in views Jan Wieck <wieck@debis.com>
  0 siblings, 1 reply; 10+ messages in thread

From: Rick Delaney @ 2000-01-05 23:13 UTC (permalink / raw)
  To: Jan Wieck <wieck@debis.com>; +Cc: pgsql-sql

Jan Wieck wrote:
> 
> Rick Delaney wrote:
> 
> > Jan Wieck wrote:
> > >
> > >      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.
> >
> > I'd have to take a good look at all the expressions again to know if I could do
> > this but I think I probably can.  Only, with what I've started on I'm already
> > getting "rule plan string too big" errors.  (What I've started on is just one
> > view that doesn't have all the expressions in it yet).
> 
>      That's what I meant with "might be unable...". You talked about a 50
>      column view with complicated expressions, and that's surely too large for
>      the current implementation.
> 
> > So I will just get the same thing with nested views, no?  That is how I
                           ^^^^^^^^^^                    ^^
> > understand the "The Postgres Rule System" section of the manual and Tom Lane's
> > answer to my post.
> 
>      As the author of "The Postgres Rule System" I say yes, except that it
                                                   ^^^^^^^^^
>      requires some recursions in the rewriter. Thus, it will take a little

Sorry to be stupid but with my terribly phrased question I can't tell if you mean
"Yes, you are correct that you will still get 'rule plan string too big' errors
with nested views" or "Yes, nested views will work fine as I described despite
your erroneous interpretation of what you read (but you may have to fix the
recursion limit as I already said)".  :-)

I'm 99% sure you mean the latter, now, after re-reading "The Postgres Rule
System" and your first reply to me.  I guess the only reason I'm not 100% sure is
I don't understand where the 'rule plan string too big' limit comes from.  Is it
in the storage of the view/rule (related to the 8K limit on row size)?  I guess
that would make sense.

I suppose if I re-read enough times I'll get it.

Thanks again,

--Rick



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: [SQL] Calculation dependencies in views
  2000-01-05 00:14 Calculation dependencies in views Rick Delaney <rick@consumercontact.com>
  2000-01-05 01:03 ` Re: [SQL] Calculation dependencies in views Jan Wieck <wieck@debis.com>
  2000-01-05 20:15   ` Re: [SQL] Calculation dependencies in views Rick Delaney <rick@consumercontact.com>
  2000-01-05 21:49     ` Re: [SQL] Calculation dependencies in views Jan Wieck <wieck@debis.com>
  2000-01-05 23:13       ` Re: [SQL] Calculation dependencies in views Rick Delaney <rick@consumercontact.com>
@ 2000-01-06 00:10         ` Jan Wieck <wieck@debis.com>
  2000-01-06 01:47           ` Re: [SQL] Calculation dependencies in views Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 10+ messages in thread

From: Jan Wieck @ 2000-01-06 00:10 UTC (permalink / raw)
  To: Rick Delaney <rick@consumercontact.com>; +Cc: pgsql-sql

Rick Delaney wrote:

> Jan Wieck wrote:
> >
> >      As the author of "The Postgres Rule System" I say yes, except that it
>                                                    ^^^^^^^^^
> >      requires some recursions in the rewriter. Thus, it will take a little
>
> Sorry to be stupid but with my terribly phrased question I can't tell if you mean
> "Yes, you are correct that you will still get 'rule plan string too big' errors
> with nested views" or "Yes, nested views will work fine as I described despite
> your erroneous interpretation of what you read (but you may have to fix the
> recursion limit as I already said)".  :-)
>
> I'm 99% sure you mean the latter, now, after re-reading "The Postgres Rule
> System" and your first reply to me.  I guess the only reason I'm not 100% sure is
> I don't understand where the 'rule plan string too big' limit comes from.  Is it
> in the storage of the view/rule (related to the 8K limit on row size)?  I guess
> that would make sense.

     Stop (rereading) recursion now - you got it.

     It's exactly the storage of rules (views are implemented via rewriting
     rules) in combo with the 8K limit, that causes this "rule plan string too
     big" error. Just the the view/rule creation utility does some extra check
     for it.

     Rules are stored as a special kind of printable querytree string. A
     querytree is the systems internal representation of a query, and they are
     really verbose and thus - well - huge (compared to what the user typed in).

     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.


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






^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: [SQL] Calculation dependencies in views
  2000-01-05 00:14 Calculation dependencies in views Rick Delaney <rick@consumercontact.com>
  2000-01-05 01:03 ` Re: [SQL] Calculation dependencies in views Jan Wieck <wieck@debis.com>
  2000-01-05 20:15   ` Re: [SQL] Calculation dependencies in views Rick Delaney <rick@consumercontact.com>
  2000-01-05 21:49     ` Re: [SQL] Calculation dependencies in views Jan Wieck <wieck@debis.com>
  2000-01-05 23:13       ` Re: [SQL] Calculation dependencies in views Rick Delaney <rick@consumercontact.com>
  2000-01-06 00:10         ` Re: [SQL] Calculation dependencies in views Jan Wieck <wieck@debis.com>
@ 2000-01-06 01:47           ` Tom Lane <tgl@sss.pgh.pa.us>
  2000-01-06 19:42             ` Re: [SQL] Calculation dependencies in views Rick Delaney <rick@consumercontact.com>
  0 siblings, 1 reply; 10+ messages in thread

From: Tom Lane @ 2000-01-06 01:47 UTC (permalink / raw)
  To: Jan Wieck <wieck@debis.com>; +Cc: Rick Delaney <rick@consumercontact.com>; pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: [SQL] Calculation dependencies in views
  2000-01-05 00:14 Calculation dependencies in views Rick Delaney <rick@consumercontact.com>
  2000-01-05 01:03 ` Re: [SQL] Calculation dependencies in views Jan Wieck <wieck@debis.com>
  2000-01-05 20:15   ` Re: [SQL] Calculation dependencies in views Rick Delaney <rick@consumercontact.com>
  2000-01-05 21:49     ` Re: [SQL] Calculation dependencies in views Jan Wieck <wieck@debis.com>
  2000-01-05 23:13       ` Re: [SQL] Calculation dependencies in views Rick Delaney <rick@consumercontact.com>
  2000-01-06 00:10         ` Re: [SQL] Calculation dependencies in views Jan Wieck <wieck@debis.com>
  2000-01-06 01:47           ` Re: [SQL] Calculation dependencies in views Tom Lane <tgl@sss.pgh.pa.us>
@ 2000-01-06 19:42             ` Rick Delaney <rick@consumercontact.com>
  0 siblings, 0 replies; 10+ messages in thread

From: Rick Delaney @ 2000-01-06 19:42 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Jan Wieck <wieck@debis.com>; pgsql-sql

Tom Lane wrote:
> 
> Jan Wieck <wieck@debis.com> writes:
> 
> >      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 ;-).)

Interesting.  Anyway, I grok it now.  Thank you both for all your help.

Cheers,

--Rick



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: [SQL] Calculation dependencies in views
  2000-01-05 00:14 Calculation dependencies in views Rick Delaney <rick@consumercontact.com>
@ 2000-01-05 05:35 ` Tom Lane <tgl@sss.pgh.pa.us>
  2000-01-05 20:14   ` Re: [SQL] Calculation dependencies in views Rick Delaney <rick@consumercontact.com>
  1 sibling, 1 reply; 10+ messages in thread

From: Tom Lane @ 2000-01-05 05:35 UTC (permalink / raw)
  To: Rick Delaney <rick@consumercontact.com>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: [SQL] Calculation dependencies in views
  2000-01-05 00:14 Calculation dependencies in views Rick Delaney <rick@consumercontact.com>
  2000-01-05 05:35 ` Re: [SQL] Calculation dependencies in views Tom Lane <tgl@sss.pgh.pa.us>
@ 2000-01-05 20:14   ` Rick Delaney <rick@consumercontact.com>
  0 siblings, 0 replies; 10+ messages in thread

From: Rick Delaney @ 2000-01-05 20:14 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql

Tom Lane wrote:
> 
> 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 was more interested in reducing code complexity but this is good to know
anyway.  Thanks a lot for your help.  Your last remark brings another question to
mind.

If I do this:

rick=> CREATE TABLE foo (foo numeric(9,2));
CREATE
rick=> CREATE VIEW bar AS SELECT foo * foo AS bar FROM foo;
CREATE
rick=> \d foo
Table    = foo
+----------------------------------+--------------------------+-------+
|              Field               |      Type                | Length|
+----------------------------------+--------------------------+-------+
| foo                              | numeric                  |  9.2  |
+----------------------------------+--------------------------+-------+
rick=> \d bar
View    = bar
Query   = SELECT ("foo"."foo" * "foo"."foo") AS "bar" FROM "foo";
+----------------------------------+--------------------------+-------+
|              Field               |      Type                | Length|
+----------------------------------+--------------------------+-------+
| bar                              | numeric                  |65535.65|
+----------------------------------+--------------------------+-------+
                                                               ^^^^^^^^
What governs the precision of the numeric in the view "bar"?  I couldn't find the
specifics of this in the docs.  Is it something I need to ever think of?  If so,
can I cast it to a smaller numeric?  I think 18.4 should be sufficient in this
case.

Thanks again,

--Rick



^ permalink  raw  reply  [nested|flat] 10+ messages in thread


end of thread, other threads:[~2000-01-06 19:42 UTC | newest]

Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-01-05 00:14 Calculation dependencies in views Rick Delaney <rick@consumercontact.com>
2000-01-05 01:03 ` Jan Wieck <wieck@debis.com>
2000-01-05 20:15   ` Rick Delaney <rick@consumercontact.com>
2000-01-05 21:49     ` Jan Wieck <wieck@debis.com>
2000-01-05 23:13       ` Rick Delaney <rick@consumercontact.com>
2000-01-06 00:10         ` Jan Wieck <wieck@debis.com>
2000-01-06 01:47           ` Tom Lane <tgl@sss.pgh.pa.us>
2000-01-06 19:42             ` Rick Delaney <rick@consumercontact.com>
2000-01-05 05:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-01-05 20:14   ` Rick Delaney <rick@consumercontact.com>

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