On Sat, Dec 10, 2016 at 5:42 PM, Peter J. Holzer <hjp-pgsql@hjp.at> wrote:
On 2016-12-09 21:45:35 -0500, Melvin Davidson wrote: > On Fri, Dec 9, 2016 at 6:40 PM, Samuel Williams <space.ship.traveller@gmail.com> > wrote: > >I also read that when you change a column which is not index, all the > >indexes for that row need to be updated anyway. Is that correct? > > That is not correct. Indexes are changed under the following conditions: > A. An insert is done to the table which involves an index. > B. A delete is done to the table which involves an index. > C. An update is done that involves columns included in an index. > D. An index is REINDEXed > > Indexes point to the tid of the row for which the column(s) in the index > are involved. So if columns updated are not involved in the index, > there is no need to change the index.
I don't think this is generally correct. The TID is a (block,item) tuple. It the updated version of the row doesn't fit into the same block it has to be stored in a different block, so the TID will change (AIUI there is a bit of trickery to avoid changing the TID if the new version is stored in the same block). This means that all the index entries for this row (not just for the changed field) will have to be updated. You can set fillfactor to a smaller value to make this less likely.
Yes, I see your point, but the case where the row does not fit into the same block would only occur with unlimited field types such as var[], bytea[], etc. I believe that to be the exception, and not the rule, so can we agree that we are both right in that for the general case indexes are updated as I have described and for the exception they act as you describe? --
Melvin Davidson I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.