slow rule on update

Hi,

(pg_version 7.4.2, i do run vacuum analyze on the whole database frequently
and just before executing statements below)

i dont know if anyone can help me because i dont know really where the problem
is, but i try. If any further information is needed i'll be glad to send.

my real rule much longer (more calculation instead of "+ 1") but this shortcut
has the same disadvantages in performance:

CREATE RULE ru_sp_update AS ON UPDATE TO Spiele
DO
  UPDATE punktecache SET pc_punkte = pc_punkte + 1

    FROM         Spieletipps AS stip
    NATURAL JOIN tippspieltage2spiele AS tspt2sp

    WHERE punktecache.tr_kurzname = stip.tr_kurzname
    AND punktecache.mg_name     = stip.mg_name
    AND punktecache.tspt_name   = tspt2sp.tspt_name
    AND stip.sp_id = OLD.sp_id
;

punktecache is a materialized view which should be updated by this rule

# \d punktecache
     Table "public.punktecache"
   Column    |   Type   | Modifiers
-------------+----------+-----------
 tr_kurzname | text     | not null
 mg_name     | text     | not null
 tspt_name   | text     | not null
 pc_punkte   | smallint | not null
Indexes:
    "pk_punktecache" primary key, btree (tr_kurzname, mg_name, tspt_name)
Foreign-key constraints:
    "fk_mitglieder" FOREIGN KEY (tr_kurzname, mg_name) REFERENCES
mitglieder(tr_kurzname, mg_name) ON UPDATE CASCADE ON DELETE CASCADE
    "fk_tippspieltage" FOREIGN KEY (tr_kurzname, tspt_name) REFERENCES
tippspieltage(tr_kurzname, tspt_name) ON UPDATE CASCADE ON DELETE CASCADE


my update statement:

explain analyze UPDATE spiele
SET    sp_heimtore = spup.spup_heimtore,
       sp_gasttore = spup.spup_gasttore,
       sp_abpfiff  = spup.spup_abpfiff
FROM   spieleupdates AS spup
WHERE  spiele.sp_id = spup.sp_id;

and output from explain
[did i post explain's output right? i just copied it, but i wonder if there is
a more pretty print like method to post explain's output?]


 Nested Loop  (cost=201.85..126524.78 rows=1 width=45) (actual
time=349.694..290491.442 rows=100990 loops=1)
   ->  Nested Loop  (cost=201.85..126518.97 rows=1 width=57) (actual
time=349.623..288222.145 rows=100990 loops=1)
         ->  Hash Join  (cost=201.85..103166.61 rows=4095 width=64) (actual
time=131.376..8890.220 rows=102472 loops=1)
               Hash Cond: (("outer".tspt_name = "inner".tspt_name) AND
("outer".tr_kurzname = "inner".tr_kurzname))
               ->  Seq Scan on punktecache  (cost=0.00..40970.20 rows=2065120
width=45) (actual time=0.054..4356.321 rows=2065120 loops=1)
               ->  Hash  (cost=178.16..178.16 rows=4738 width=35) (actual
time=102.259..102.259 rows=0 loops=1)
                     ->  Nested Loop  (cost=0.00..178.16 rows=4738 width=35)
(actual time=17.262..88.076 rows=10519 loops=1)
                           ->  Seq Scan on spieleupdates spup
(cost=0.00..0.00 rows=1 width=4) (actual time=0.015..0.024 rows=1 loops=1)
                           ->  Index Scan using ix_tspt2sp_fk_spiele on
tippspieltage2spiele tspt2sp  (cost=0.00..118.95 rows=4737 width=31) (actual
time=17.223..69.486 rows=10519 loops=1)
                                 Index Cond: ("outer".sp_id = tspt2sp.sp_id)
         ->  Index Scan using pk_spieletipps on spieletipps stip
(cost=0.00..5.69 rows=1 width=25) (actual time=2.715..2.717 rows=1
loops=102472)
               Index Cond: (("outer".tr_kurzname = stip.tr_kurzname) AND
("outer".mg_name = stip.mg_name) AND ("outer".sp_id = stip.sp_id))
   ->  Index Scan using pk_spiele on spiele  (cost=0.00..5.78 rows=1 width=4)
(actual time=0.012..0.014 rows=1 loops=100990)
         Index Cond: (spiele.sp_id = "outer".sp_id)
 Total runtime: 537319.321 ms


Can this be made any faster? Can you give me a hint where to start research?

My guess is that the update statement inside the rule doesnt really uses the
index on punktecache, but i dont know why and i dont know how to change it.

Any hint or help is is very appreciated.

kind regards
janning