Re: Index size

On Sat, Dec 10, 2016 at 5:42 PM, Peter J. Holzer <hjp-pgsql(at)hjp(dot)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(dot)ship(dot)traveller(at)gmail(dot)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.
> | | | hjp(at)hjp(dot)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.