Обсуждение: Calculated values

Greetings!  What is the 'best way' or 'guiding philosophy' if there is
one for dealing with calculated values in a pg database?  For example,
say you have a table with a column of floats, and you want to make a
view showing this column as a percent of the total across the column.
Should you

a) create another table with the total, and put on triggers on insert,
update, and delete to modify the right total or

b) create the view with a specific subselect to recalculate the total
at select time.  This has the disadvantage that the total seems to be
recalculated for each row.  Is there any sql syntax which can merge a
dynamically generated aggregate, *calculated only once*, into each
output row?

Thanks!


--
Camm Maguire                             camm@enhanced.com
==========================================================================
"The earth is but one country, and mankind its citizens."  --  Baha'u'llah

Camm Maguire wrote:
>
> Greetings!  What is the 'best way' or 'guiding philosophy' if there is
> one for dealing with calculated values in a pg database?  For example,
> say you have a table with a column of floats, and you want to make a
> view showing this column as a percent of the total across the column.
> Should you
>
> a) create another table with the total, and put on triggers on insert,
> update, and delete to modify the right total or
>
> b) create the view with a specific subselect to recalculate the total
> at select time.  This has the disadvantage that the total seems to be
> recalculated for each row.  Is there any sql syntax which can merge a
> dynamically generated aggregate, *calculated only once*, into each
> output row?

Hmmm ... You want to avoid "remerging", if I follow you ...

Coud you try :

CREATE VIEW my view AS
    SELECT id, partialsum, (partialsum/totalsum) AS percentage
    FROM (SELECT id, SUM(item) AS partialsum GROUP BY id)
        JOIN (SELECT SUM(item) AS totalsum); -- Note : *no* "ON" clause

That way, totalsum should be computed once.

"Caching" computations in a secondary table is a (somewhat hidden) form
of redundancy, and therefore a nice way to ask for trouble ...

Hope this helps ...

--
Emmanuel Charpentier

Greetings, and thanks for your reply!

Emmanuel Charpentier <charpent@bacbuc.dyndns.org> writes:

> Camm Maguire wrote:
> >
> > Greetings!  What is the 'best way' or 'guiding philosophy' if there is
> > one for dealing with calculated values in a pg database?  For example,
> > say you have a table with a column of floats, and you want to make a
> > view showing this column as a percent of the total across the column.
> > Should you
> >
> > a) create another table with the total, and put on triggers on insert,
> > update, and delete to modify the right total or
> >
> > b) create the view with a specific subselect to recalculate the total
> > at select time.  This has the disadvantage that the total seems to be
> > recalculated for each row.  Is there any sql syntax which can merge a
> > dynamically generated aggregate, *calculated only once*, into each
> > output row?
>
> Hmmm ... You want to avoid "remerging", if I follow you ...
>
> Coud you try :
>
> CREATE VIEW my view AS
>     SELECT id, partialsum, (partialsum/totalsum) AS percentage
>     FROM (SELECT id, SUM(item) AS partialsum GROUP BY id)
>         JOIN (SELECT SUM(item) AS totalsum); -- Note : *no* "ON" clause
>

I can't seem to get this syntax to work with pg.  No subselects seem
to be accepted in the from clause, and join doesn't seem to be a
reserved word at all.  But this kind of thing is exactly what I'm
looking for.

Another idea is to write a plpgsql function which returns tuples of a
given table.  Only problem here appears to be that to access the
columns of the result, one has to do something like 'select
id(function),percentage(function),...' which then calls the function
multiple times for each column!

Maybe this is better done outside the db.

Any advice most appreciated!

> That way, totalsum should be computed once.
>
> "Caching" computations in a secondary table is a (somewhat hidden) form
> of redundancy, and therefore a nice way to ask for trouble ...
>
> Hope this helps ...
>
> --
> Emmanuel Charpentier
>
>

--
Camm Maguire                             camm@enhanced.com
==========================================================================
"The earth is but one country, and mankind its citizens."  --  Baha'u'llah

Camm Maguire <camm@enhanced.com> writes:
> Emmanuel Charpentier <charpent@bacbuc.dyndns.org> writes:
>> CREATE VIEW my view AS
>> SELECT id, partialsum, (partialsum/totalsum) AS percentage
>> FROM (SELECT id, SUM(item) AS partialsum GROUP BY id)
>> JOIN (SELECT SUM(item) AS totalsum); -- Note : *no* "ON" clause

> I can't seem to get this syntax to work with pg.  No subselects seem
> to be accepted in the from clause, and join doesn't seem to be a
> reserved word at all.

Sounds like you are trying to do it in 7.0 or before.  Emmanuel is
relying on 7.1 features --- and the example won't work as given anyway,
since (a) the subselects neglect to specify source tables; (b) you
have to write CROSS JOIN not JOIN if you want to omit ON/USING.

In 7.0 you could accomplish the same thing with temp tables, or more
straightforwardly by something like

SELECT    id,
    SUM(item) AS partialsum,
    SUM(item) / (SELECT SUM(item) FROM table) AS percentage
FROM table
GROUP BY id

This relies for efficiency on the poorly-documented fact that the
sub-select will only be evaluated once, since it has no dependency
on the state of the outer select.  (You can check this by seeing that
EXPLAIN shows the subselect as an InitPlan not a SubPlan.)

            regards, tom lane

Greetings, and thanks so much for your reply!

Tom Lane <tgl@sss.pgh.pa.us> writes:

> Camm Maguire <camm@enhanced.com> writes:
> > Emmanuel Charpentier <charpent@bacbuc.dyndns.org> writes:
> >> CREATE VIEW my view AS
> >> SELECT id, partialsum, (partialsum/totalsum) AS percentage
> >> FROM (SELECT id, SUM(item) AS partialsum GROUP BY id)
> >> JOIN (SELECT SUM(item) AS totalsum); -- Note : *no* "ON" clause
>
> > I can't seem to get this syntax to work with pg.  No subselects seem
> > to be accepted in the from clause, and join doesn't seem to be a
> > reserved word at all.
>
> Sounds like you are trying to do it in 7.0 or before.  Emmanuel is
> relying on 7.1 features --- and the example won't work as given anyway,
> since (a) the subselects neglect to specify source tables; (b) you
> have to write CROSS JOIN not JOIN if you want to omit ON/USING.
>

Thanks!  Indeed, I'm using 7.0.3.

> In 7.0 you could accomplish the same thing with temp tables, or more
> straightforwardly by something like
>
> SELECT    id,
>     SUM(item) AS partialsum,
>     SUM(item) / (SELECT SUM(item) FROM table) AS percentage
> FROM table
> GROUP BY id


>
> This relies for efficiency on the poorly-documented fact that the
> sub-select will only be evaluated once, since it has no dependency
> on the state of the outer select.  (You can check this by seeing that
> EXPLAIN shows the subselect as an InitPlan not a SubPlan.)

Thanks again.  Alas, the 'explain' for me still shows the subplan, and
I can see why.  Here is my view definition:

create view csp2 as
    SELECT c1.asof, c1.ticker, c1.sp, c1.price AS p1,
    (((100 * float8(c1.sp)) * c1.price) /
        (select sum(price*sp) from sprices,sinfo
            where sprices.sinfo = sinfo.sinfo
            and sprices.asof = c1.asof)) AS wt,
    c2.price AS p2, c2.split, c1.div,
    (100 * c1.ret)
    FROM csp1 c1, csp1 c2,  dates
    WHERE ((((c1.asof = dates.asof))
        AND (c2.asof = dates.nasof))
        AND (c1.id = c2.id));

What is obviously doing this is the 'sprices.asof = c1.asof'
dependency between the inner and outer select.  Trouble is, my only
intention is to be able to use this view with a constant, albeit
'runtime-selectable', 'asof' or date, as in

select * from csp where asof = '20000103';

Any other suggestions?  This dependency issue slows the above query
down significantly, resulting in the calculation of the same sum ~
1000 times.  Do you also agree with the previous respondent that
trying to have a table of sums, updated dynamically with triggers, is
not a good idea?  I'm trying to find the right philosophy to the
design of this db, and am ending up searching for a happy medium
between select speed and insert complexity.


>
>             regards, tom lane
>
>

Thanks again,

--
Camm Maguire                             camm@enhanced.com
==========================================================================
"The earth is but one country, and mankind its citizens."  --  Baha'u'llah

hello.

this might be as much of a general database question as it is a postgres
question...

i have a table with 5 columns...a primary key (integer), three small (10
character) text fields, and one semi-large (1400 characters) text field.
note that only a small percentage (5% ?) of the rows contain 1400 characters
in the 5th column...the other 95% have approx 10 characters.  it has 1100
rows.

the problem is this - queries (command line) often take a very long time -
anywhere from 5-15 seconds - to execute.  the queries use only the primary
key and nothing else in the where clause.  no joins.  a sample query is:

select * from weather where weatherid = 12372;

from the command line, it seems that the first query can take a very long
time but subsequent queries happen quickly ( < 1 sec).  i'm guessing that
this is the result of caching or something.

do the long times make sense?  what can i do to shorten them?  would a
smaller text field help?  i have no reason to think that this would be
faster or slower in another db, so it might be unrelated to postgres itself.

i greatly appreciate your help.

chris

First thing to try is to do

explain select * from weather where weatherid = 12372;

and see if it's doing a sequential scan on your table.
If it is, the index and table stats may be out of date
and I think you fix those using vacuum analyze.

One other thing is that if you only have 1100 rows of the sizes
you describe, even a table scan shouldn't take as long as you
say unless the machine is either under serious load or doesn't
have enough memory to run postgres without swapping. (or the disk is
freaking out with io errors)

Alex.

On Mon, 29 Jan 2001, chris markiewicz wrote:

> hello.
>
> this might be as much of a general database question as it is a postgres
> question...
>
> i have a table with 5 columns...a primary key (integer), three small (10
> character) text fields, and one semi-large (1400 characters) text field.
> note that only a small percentage (5% ?) of the rows contain 1400 characters
> in the 5th column...the other 95% have approx 10 characters.  it has 1100
> rows.
>
> the problem is this - queries (command line) often take a very long time -
> anywhere from 5-15 seconds - to execute.  the queries use only the primary
> key and nothing else in the where clause.  no joins.  a sample query is:
>
> select * from weather where weatherid = 12372;
>
> from the command line, it seems that the first query can take a very long
> time but subsequent queries happen quickly ( < 1 sec).  i'm guessing that
> this is the result of caching or something.
>
> do the long times make sense?  what can i do to shorten them?  would a
> smaller text field help?  i have no reason to think that this would be
> faster or slower in another db, so it might be unrelated to postgres itself.
>
> i greatly appreciate your help.
>
> chris
>

* adb <adb@Beast.COM> [010129 13:40] wrote:
>
> One other thing is that if you only have 1100 rows of the sizes
> you describe, even a table scan shouldn't take as long as you
> say unless the machine is either under serious load or doesn't
> have enough memory to run postgres without swapping. (or the disk is
> freaking out with io errors)

Increasing the amount of shared memory for postgresql can help.

vacuum analyze seems to have done the trick...this is the sort of thing that
happens when a non-dba is doing dba work...

sincerest thanks to all that responded!

chris

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of adb
Sent: Monday, January 29, 2001 4:36 PM
To: chris markiewicz
Cc: pgsql-general@postgresql.org
Subject: Re: performance...


First thing to try is to do

explain select * from weather where weatherid = 12372;

and see if it's doing a sequential scan on your table.
If it is, the index and table stats may be out of date
and I think you fix those using vacuum analyze.

One other thing is that if you only have 1100 rows of the sizes
you describe, even a table scan shouldn't take as long as you
say unless the machine is either under serious load or doesn't
have enough memory to run postgres without swapping. (or the disk is
freaking out with io errors)

Alex.

On Mon, 29 Jan 2001, chris markiewicz wrote:

> hello.
>
> this might be as much of a general database question as it is a postgres
> question...
>
> i have a table with 5 columns...a primary key (integer), three small (10
> character) text fields, and one semi-large (1400 characters) text field.
> note that only a small percentage (5% ?) of the rows contain 1400
characters
> in the 5th column...the other 95% have approx 10 characters.  it has 1100
> rows.
>
> the problem is this - queries (command line) often take a very long time -
> anywhere from 5-15 seconds - to execute.  the queries use only the primary
> key and nothing else in the where clause.  no joins.  a sample query is:
>
> select * from weather where weatherid = 12372;
>
> from the command line, it seems that the first query can take a very long
> time but subsequent queries happen quickly ( < 1 sec).  i'm guessing that
> this is the result of caching or something.
>
> do the long times make sense?  what can i do to shorten them?  would a
> smaller text field help?  i have no reason to think that this would be
> faster or slower in another db, so it might be unrelated to postgres
itself.
>
> i greatly appreciate your help.
>
> chris
>

You have weatherid indexed, right? If not, index it and run VACUUM
ANALYZE -- if so make then sure you've run VACUUM ANALYZE after you
created the index..

Also, you might want to EXPLAIN that query to get the query plan (just
type EXPLAIN <query>)if the above doesn't work. Post the ouput of
EXPLAIN along with the schema of your tables and perhaps someone can help
some more..

-Mitch


On Mon, 29 Jan 2001, chris markiewicz wrote:

> hello.
>
> this might be as much of a general database question as it is a postgres
> question...
>
> i have a table with 5 columns...a primary key (integer), three small (10
> character) text fields, and one semi-large (1400 characters) text field.
> note that only a small percentage (5% ?) of the rows contain 1400 characters
> in the 5th column...the other 95% have approx 10 characters.  it has 1100
> rows.
>
> the problem is this - queries (command line) often take a very long time -
> anywhere from 5-15 seconds - to execute.  the queries use only the primary
> key and nothing else in the where clause.  no joins.  a sample query is:
>
> select * from weather where weatherid = 12372;
>
> from the command line, it seems that the first query can take a very long
> time but subsequent queries happen quickly ( < 1 sec).  i'm guessing that
> this is the result of caching or something.
>
> do the long times make sense?  what can i do to shorten them?  would a
> smaller text field help?  i have no reason to think that this would be
> faster or slower in another db, so it might be unrelated to postgres itself.
>
> i greatly appreciate your help.
>
> chris
>
>