Optimizations - Mailing list pgsql-general

We run a student scoring system with PostgreSQL as a backend. After the results for each student are inputted into the
system,we display many reports for them. We haven't had a problem with efficiency or speed, but it has come up that
perhapsstoring the rolled up scores of each student may be better than calculating their score on the fly. I have
alwayscoded the SQL to calculate on the fly and do not see any benefit from calculating on the fly. For a test with
over100 questions and with 950 students having taken it, it calculates all their relevant score information in less
thanhalf a second. Would there be any obvious benefit to caching the results? 

I would greatly appreciate any thoughts on this.

Here is the structure:

A database to store and calculate student results.


Information about the student and which test they took:

\d test_registration;
                      Table "public.test_registration"
       Column        |            Type             |       Modifiers
---------------------+-----------------------------+------------------------
 id                  | uuid                        | not null
 sid                 | character varying(36)       | not null
 created_date        | timestamp without time zone | not null default now()
 modified_date       | timestamp without time zone | not null
 test_administration | uuid                        | not null


The actual results (what the student marked):

\d test_registration_result (linked to test_registration.id above)


        Table "public.test_registration_result"
      Column       |         Type          | Modifiers
-------------------+-----------------------+-----------
 test_registration | uuid                  | not null
 question          | uuid                  | not null
 answer            | character varying(15) |


\d question (information on each question)

                  Table "public.question"
      Column       |          Type          |   Modifiers
-------------------+------------------------+---------------
 id                | uuid                   | not null
 test              | uuid                   | not null
 question          | integer                | not null
 weight            | double precision       |


\d question_answer (the answers for the question)
        Table "public.question_answer"
  Column  |         Type          | Modifiers
----------+-----------------------+-----------
 question | uuid                  | not null
 answer   | character varying(15) | not null



With a SQL query:

SELECT sid, raw_score, weighted_score, number_questions, total_weights,
( weighted_score / total_weights ) * 100.00 as mp_percentage,
total_weights
    FROM
    (
        SELECT
        tr.sid as sid,
        sum (
              (
                SELECT (case when a.answer = r.answer then 1 else 0 end )
              )
             ) as raw_score,
         sum (
               (
                 SELECT (case when a.answer = r.answer THEN q.weight end )
                 )
              ) as weighted_score,
.....


For 953 students on a test with 145 questions, this takes less than half a second to calculate. Is is worth storing the
score?

\d score_set
                           Table "public.score_set"
         Column         |            Type             |       Modifiers
------------------------+-----------------------------+------------------------
 id                     | uuid                        | not null
 sid                    | uuid                        | not null
 test_registration_id   | uuid                        | not null
 test_administration_id | uuid                        | not null
 score                  | double precision            | not null

Will it be much faster? I know more storage will be needed.


Thank you

Ogden