Обсуждение: How jsonb updates affect GIN indexes
Hi
When a jsonb column is updated, as far as I understand the entire column is updated, even though perhaps only one of the keys has a modified value.
My question is: if I have a GIN index on that column, will PG visit every key and update each index entry, or will it visit every key and update each index entry only if the value is different, or will it only visit those keys that have been modified?
I have a jsonb column where I only need to index two keys, and was wondering if there is any benefit in terms of update efficiency to having two indexes, one for each key, rather than just creating a GIN index on the whole (rather large) column.
Thanks,
Eric
Eric: On Sat, Feb 27, 2016 at 11:46 AM, Eric Mortensen <eric@appstax.com> wrote: > When a jsonb column is updated, as far as I understand the entire column is > updated, even though perhaps only one of the keys has a modified value. Also, bear in mind when a column is updated the tuple is too, and MVCC treats it similarly to a delete+insert, so it may move around ( as the old version may be needed by some transaction, and not enough space may be free on the original location ). and every index reference ( GIN or whatever, to any column ) needs to be updated. I do not know the details, I just know it's a complex decision, someone with more knowledge of the internals may give you a more acurate descriptin if needed. Francisco Olarte.
CCing to list to maintain context. On Sat, Feb 27, 2016 at 12:14 PM, Eric Mortensen <eric@appstax.com> wrote: > Thanks Francisco, I had not considered MVCC. If that is true, it would seem > to me that a GIN index would "always" be less efficient, as it potentially > would have to update every key's posting tree if a tuple moves, whereas two > btree indexes would only require modififying two trees. If you are that worried about performance, maybe your data will be best served by splitting those fields out of the jsonb or even splitting the table. As always, measure, identify bottleneck. Francisco Olarte.