X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id C023632A626 for ; Tue, 5 Oct 2004 23:55:36 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 82753-05 for ; Tue, 5 Oct 2004 22:55:32 +0000 (GMT) Received: from pw6tiger.de (pw6tiger.de [217.160.140.116]) by svr1.postgresql.org (Postfix) with SMTP id EFA5A32A130 for ; Tue, 5 Oct 2004 23:55:32 +0100 (BST) Received: (qmail 29414 invoked by uid 505); 5 Oct 2004 22:55:29 -0000 Received: from vygen@gmx.de by planwerk6 by uid 89 with qmail-scanner-1.14 (virus scan: Clear:. Processed in 0.390171 secs); 05 Oct 2004 22:55:29 -0000 Received: from p508cd17a.dip0.t-ipconnect.de (HELO ?192.168.1.102?) (vygen@planwerk6.de@80.140.209.122) by pw6tiger.de with SMTP; 5 Oct 2004 22:55:28 -0000 From: Janning Vygen To: pgsql-performance@postgresql.org Subject: slow rule on update Date: Wed, 6 Oct 2004 00:55:04 +0200 User-Agent: KMail/1.7 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200410060055.06211.vygen@gmx.de> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200410/48 X-Sequence-Number: 8524 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