Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 08D6E9FB50E for ; Sat, 21 Apr 2007 07:42:03 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 71964-09 for ; Sat, 21 Apr 2007 07:41:55 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from floppy.pyrenet.fr (floppy.pyrenet.fr [194.116.145.2]) by postgresql.org (Postfix) with ESMTP id B648D9FB509 for ; Sat, 21 Apr 2007 07:41:58 -0300 (ADT) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id 59CB33090A; Sat, 21 Apr 2007 12:41:57 +0200 (MET DST) From: cluster X-Newsgroups: pgsql.performance Subject: Re: FK triggers misused? Date: Sat, 21 Apr 2007 12:43:27 +0200 Organization: Hub.Org Networking Services Lines: 54 Message-ID: References: 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/20070403) In-Reply-To: To: pgsql-performance@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.297 tagged_above=0 required=5 tests=AWL=0.296, BAYES_50=0.001 X-Spam-Level: X-Archive-Number: 200704/356 X-Sequence-Number: 24233 I have investigated a bit now and found the following: When I perform the update the *first* time, the triggers are actually not evaluated. But from the second update they are. Also notice that the number of rows changes. Shouldn't that number of rows always be 2 as question_id is primary key? Example: => explain analyze update questions set cancelled_time = now() where question_id in (10,11); QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on questions (cost=4.01..12.04 rows=2 width=112) (actual time=0.193..0.205 rows=2 loops=1) Recheck Cond: ((question_id = 10) OR (question_id = 11)) -> BitmapOr (cost=4.01..4.01 rows=2 width=0) (actual time=0.046..0.046 rows=0 loops=1) -> Bitmap Index Scan on questions_pkey (cost=0.00..2.00 rows=1 width=0) (actual time=0.037..0.037 rows=1 loops=1) Index Cond: (question_id = 10) -> Bitmap Index Scan on questions_pkey (cost=0.00..2.00 rows=1 width=0) (actual time=0.005..0.005 rows=1 loops=1) Index Cond: (question_id = 11) Trigger for constraint questions_repost_of_fkey: time=0.023 calls=2 Total runtime: 0.734 ms (9 rows) => explain analyze update questions set cancelled_time = now() where question_id in (10,11); QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on questions (cost=4.01..12.04 rows=2 width=112) (actual time=0.085..0.097 rows=2 loops=1) Recheck Cond: ((question_id = 10) OR (question_id = 11)) -> BitmapOr (cost=4.01..4.01 rows=2 width=0) (actual time=0.047..0.047 rows=0 loops=1) -> Bitmap Index Scan on questions_pkey (cost=0.00..2.00 rows=1 width=0) (actual time=0.036..0.036 rows=2 loops=1) Index Cond: (question_id = 10) -> Bitmap Index Scan on questions_pkey (cost=0.00..2.00 rows=1 width=0) (actual time=0.007..0.007 rows=2 loops=1) Index Cond: (question_id = 11) Trigger for constraint questions_repost_of_fkey: time=0.025 calls=2 Trigger for constraint questions_author_id_fkey: time=0.167 calls=2 Trigger for constraint questions_category_id_fkey: time=0.196 calls=2 Trigger for constraint questions_lock_user_id_fkey: time=0.116 calls=2 Total runtime: 1.023 ms (12 rows)