Re: FK triggers misused?

От: cluster
Тема: Re: FK triggers misused?
Дата: ,
Msg-id: f0cppe$2vr0$1@news.hub.org
(см: обсуждение, исходный текст)
Ответ на: FK triggers misused?  (cluster)
Ответы: Re: FK triggers misused?  (Stephan Szabo)
Список: pgsql-performance

Скрыть дерево обсуждения

FK triggers misused?  (cluster, )
 Re: FK triggers misused?  (Tom Lane, )
 Re: FK triggers misused?  (Andrew - Supernews, )
  Re: FK triggers misused?  (Tom Lane, )
 Re: FK triggers misused?  (Andrew - Supernews, )
  Re: FK triggers misused?  (cluster, )
   Re: FK triggers misused?  (Tom Lane, )
   Re: FK triggers misused?  (Tom Lane, )
    Re: FK triggers misused?  (cluster, )
 Re: FK triggers misused?  (cluster, )
  Re: FK triggers misused?  (Stephan Szabo, )

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)




В списке pgsql-performance по дате сообщения:

От: Stephan Szabo
Дата:
Сообщение: Re: FK triggers misused?
От: Tom Lane
Дата:
Сообщение: Re: Odd problem with planner choosing seq scan