Skip site navigation (1) Skip section navigation (2)

FK triggers misused?

From: cluster <skrald(at)amossen(dot)dk>
To: pgsql-performance(at)postgresql(dot)org
Subject: FK triggers misused?
Date: 2007-04-14 13:51:37
Message-ID: evqm6g$29b1$ (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
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

Why are these FK triggers evaluated at all and why do they take so much

=> 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)


pgsql-performance by date

Next:From: Tom LaneDate: 2007-04-14 15:52:45
Subject: Re: [HACKERS] choose_bitmap_and again (was Re: Strangely Variable Query Performance)
Previous:From: Bill MoranDate: 2007-04-14 11:59:47
Subject: Re: Basic Q on superfluous primary keys

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group