FK triggers misused?

Поиск
Список
Период
Сортировка
От cluster
Тема FK triggers misused?
Дата
Msg-id evqm6g$29b1$1@news.hub.org
обсуждение исходный текст
Ответы Re: FK triggers misused?  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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
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)

В списке pgsql-performance по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: [HACKERS] choose_bitmap_and again (was Re: Strangely Variable Query Performance)
Следующее
От: Tom Lane
Дата:
Сообщение: Re: FK triggers misused?