Re: Index on two columns not used

От: Tom Lane
Тема: Re: Index on two columns not used
Дата: ,
Msg-id: 24757.1161619301@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: Re: Index on two columns not used  (Markus Schaber)
Ответы: Re: Index on two columns not used  (Markus Schaber)
Список: pgsql-performance

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

Index on two columns not used  (Arnaud Lesauvage, )
 Re: Index on two columns not used  ("Heikki Linnakangas", )
  Re: Index on two columns not used  (Arnaud Lesauvage, )
   Re: Index on two columns not used  ("Heikki Linnakangas", )
    Re: Index on two columns not used  (Arnaud Lesauvage, )
     Re: Index on two columns not used  ("Heikki Linnakangas", )
      Re: Index on two columns not used  (Arnaud Lesauvage, )
  Re: Index on two columns not used  (Péter Kovács, )
   Re: Index on two columns not used  (Markus Schaber, )
    Re: Index on two columns not used  (Alvaro Herrera, )
     Re: Index on two columns not used  (Markus Schaber, )
      Re: Index on two columns not used  (Tom Lane, )
       Re: Index on two columns not used  (Markus Schaber, )
    Re: Index on two columns not used  (Péter Kovács, )
 Re: Index on two columns not used  (Tom Lane, )
  Re: Index on two columns not used  (Arnaud Lesauvage, )

Markus Schaber <> writes:
> Alvaro Herrera wrote:
>> Actually, when the UPDATE puts a new row version in the same heap page,
>> the index must be updated anyway.

> AFAICS only, when the index covers (directly or via function) a column
> that's actually changed.
> Changing columns the index does not depend on should not need any write
> access to that index.
> Correct me if I'm wrong.

You're wrong.  An UPDATE always writes a new version of the row (if it
overwrote the row in-place, it wouldn't be rollback-able).  The new
version has a different TID and therefore the index entry must change.
To support MVCC, our approach is to always insert a new index entry
pointing at the new TID --- the old one remains in place so that the old
version can still be found by transactions that need it.  Once the old
row version is entirely dead, VACUUM is responsible for removing both it
and the index entry pointing at it.

Other DBMSes use other approaches that shift the overhead to other
places, but that's how Postgres does it.

            regards, tom lane


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

От: "Jim C. Nasby"
Дата:
Сообщение: Re: Best COPY Performance
От: "Jim C. Nasby"
Дата:
Сообщение: Re: Optimizing disk throughput on quad Opteron