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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
