On Sat, Dec 10, 2016 at 5:42 PM, Peter J. Holzer <hjp-pg...@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.travel...@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. > > hp > > -- > _ | Peter J. Holzer | A coding theorist is someone who doesn't > |_|_) | | think Alice is crazy. > | | | h...@hjp.at | -- John Gordon > __/ | http://www.hjp.at/ | http://downlode.org/Etext/alicebob.html > *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.
