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)
