Re: Running update in chunks?

On 25/01/13 11:38, Tim Uckun wrote:
>> That suggests (to me, at least) that it is related to index updating. Again,
>> your GIN index seems primary candidate.
>>
>> Try running iostat (I think that's available on a Mac) with/without the
>> fillfactor and with/without the GIN index while you do the updates. It's
>> possible your SSD is just behaving oddly under stress.
>>
>
> I dropped the index and the numbers shot up tenfold or more. I don't
> know why postgres feels the need to update the GIN index on the hstore
> field when I am only updating an integer field but it looks like I
> need to split the hstore into a different table.
If the row moves to a different block, then it has no choice. The old
index entry will point to an invalid block. There are some optimisations
(HOT - http://pgsql.tapoueh.org/site/html/misc/hot.html) but that relies
on (iirc) the update staying on the same block and also not updating any
indexed fields (and you were, I think).

A GIN index is very expensive to update compared to btree too.
--
Richard Huxton
Archonet Ltd