From: cluster <skrald@amossen.dk>
To: pgsql-performance@postgresql.org
Subject: FK triggers misused?
Date: Sat, 14 Apr 2007 15:51:37 +0200
Message-ID: <evqm6g$29b1$1@news.hub.org> (raw)
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)
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: <evqm6g$29b1$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