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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
