Re: how to efficiently update tuple in many-to-many relationship?

От: Tom Lane
Тема: Re: how to efficiently update tuple in many-to-many relationship?
Дата: ,
Msg-id: 26925.1176221294@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: Re: how to efficiently update tuple in many-to-many relationship?  (Drew Wilson)
Ответы: Question about memory allocations  (Steve)
Список: pgsql-performance

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

how to efficiently update tuple in many-to-many relationship?  (Drew Wilson, )
 Re: how to efficiently update tuple in many-to-many relationship?  ("Merlin Moncure", )
 Re: how to efficiently update tuple in many-to-many relationship?  (Tom Lane, )
  Re: how to efficiently update tuple in many-to-many relationship?  (Drew Wilson, )
   Re: how to efficiently update tuple in many-to-many relationship?  (Tom Lane, )
    Re: how to efficiently update tuple in many-to-many relationship?  (Drew Wilson, )
     Re: how to efficiently update tuple in many-to-many relationship?  (Tom Lane, )
      Re: how to efficiently update tuple in many-to-many relationship?  (Drew Wilson, )
       Re: how to efficiently update tuple in many-to-many relationship?  (Tom Lane, )
        Question about memory allocations  (Steve, )
         Re: Question about memory allocations  (Tom Lane, )
          Re: Question about memory allocations  (Steve, )
         Re: Question about memory allocations  (Greg Smith, )
          Re: Question about memory allocations  (Steve, )
         Re: Question about memory allocations  (Andrew McMillan, )
          Re: Question about memory allocations  (Steve, )
           Re: Question about memory allocations  (Ron, )
            Re: Question about memory allocations  (Tom Lane, )
           Re: Question about memory allocations  (Carlos Moreno, )
            Re: Question about memory allocations  ("Jan de Visser", )

Drew Wilson <> writes:
> If I understand the EXPLAIN ANALYZE results below, it looks like the
> time spent applying the "set is_public = true" is much much more than
> the fetch. I don't see any triggers firing.

Nope, there aren't any.  8.2 is smart enough to bypass firing FK
triggers on UPDATE if the key columns didn't change.  Of course that
check takes a certain amount of time, but I don't think it's a large
amount.  So basically we're looking at index update and WAL logging
as the major costs here, I think.

[ thinks for a bit... ]  Part of the problem might be that the working
set for updating all the indexes is too large.  What do you have
shared_buffers set to, and can you increase it?

Oh, and checkpoint_segments at 8 is probably still not nearly enough;
if you have disk space to spare, try something like 30 (which'd eat
about 1Gb of disk space).  It might be educational to set
checkpoint_warning to 300 or so and watch the log to see how often
checkpoints happen during the update --- you want them at least a couple
minutes apart even during max load.

Also, bumping up wal_buffers a little might help some, for large updates
like this.  I've heard people claim that values as high as 64 are helpful.

            regards, tom lane


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

От: Devrim GÜNDÜZ
Дата:
Сообщение: Re: Do I need to rebuild php-pgsql for 8.2.3
От: Tobias Brox
Дата:
Сообщение: Long running transactions again ...