From: cluster <skrald@amossen.dk>
To: pgsql-performance@postgresql.org
Subject: Re: FK triggers misused?
Date: Sat, 21 Apr 2007 12:43:27 +0200
Message-ID: <f0cppe$2vr0$1@news.hub.org> (raw)
In-Reply-To: <eve6md$1cjo$1@news.hub.org>
References: <eve6md$1cjo$1@news.hub.org>
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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: skrald@amossen.dk
Subject: Re: FK triggers misused?
In-Reply-To: <f0cppe$2vr0$1@news.hub.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox