Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id E646E9FB3E6 for ; Sat, 14 Apr 2007 10:50:22 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 43119-01-9 for ; Sat, 14 Apr 2007 10:50:13 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from floppy.pyrenet.fr (news.pyrenet.fr [194.116.145.2]) by postgresql.org (Postfix) with ESMTP id CD9809FB425 for ; Sat, 14 Apr 2007 10:50:18 -0300 (ADT) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id 37DCF30909; Sat, 14 Apr 2007 15:50:17 +0200 (MET DST) From: cluster X-Newsgroups: pgsql.performance Subject: FK triggers misused? Date: Sat, 14 Apr 2007 15:51:37 +0200 Organization: Hub.Org Networking Services Lines: 48 Message-ID: Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@news.hub.org User-Agent: Thunderbird 1.5.0.10 (X11/20070306) To: pgsql-performance@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=BAYES_50=0.001 X-Spam-Level: X-Archive-Number: 200704/310 X-Sequence-Number: 24187 I have performance problem with the following simple update query: UPDATE posts SET num_views = num_views + 1 WHERE post_id IN (2526,5254,2572,4671,25); The table "posts" is a large table with a number of foreign keys (FK). It seems that the FK triggers for the table are evaluated even though none of the FK columns are altered. In fact, these FK triggers seems to constitute a considerable part of the total execution time. See the below EXPLAIN ANALYZE. Why are these FK triggers evaluated at all and why do they take so much time? ------ => EXPLAIN ANALYZE update posts set num_views = num_views + 1 where post_id in (2526,5254,2572,4671,25); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on posts (cost=10.02..29.81 rows=5 width=1230) (actual time=0.146..0.253 rows=5 loops=1) Recheck Cond: ((post_id = 2526) OR (post_id = 5254) OR (post_id = 2572) OR (post_id = 4671) OR (post_id = 25)) -> BitmapOr (cost=10.02..10.02 rows=5 width=0) (actual time=0.105..0.105 rows=0 loops=1) -> Bitmap Index Scan on posts_pkey (cost=0.00..2.00 rows=1 width=0) (actual time=0.053..0.053 rows=2 loops=1) Index Cond: (post_id = 2526) -> Bitmap Index Scan on posts_pkey (cost=0.00..2.00 rows=1 width=0) (actual time=0.012..0.012 rows=2 loops=1) Index Cond: (post_id = 5254) -> Bitmap Index Scan on posts_pkey (cost=0.00..2.00 rows=1 width=0) (actual time=0.008..0.008 rows=2 loops=1) Index Cond: (post_id = 2572) -> Bitmap Index Scan on posts_pkey (cost=0.00..2.00 rows=1 width=0) (actual time=0.010..0.010 rows=2 loops=1) Index Cond: (post_id = 4671) -> Bitmap Index Scan on posts_pkey (cost=0.00..2.00 rows=1 width=0) (actual time=0.011..0.011 rows=2 loops=1) Index Cond: (post_id = 25) Trigger for constraint posts_question_id_fkey: time=50.031 calls=5 Trigger for constraint posts_author_id_fkey: time=22.330 calls=5 Trigger for constraint posts_language_id_fkey: time=1.282 calls=5 Trigger posts_tsvectorupdate: time=61.659 calls=5 Total runtime: 174.230 ms (18 rows)