Re: column updates

Thanks. Good to hear that the easiest solution for me is also the fastest.

On Fri, 29 Oct 2004, Martijn van Oosterhout wrote:

> The number of dead tuples is the number of UPDATE statements you make.
> You if you just do a straight UPDATE x set f1=x, f2=y, ... that will be
> as optimal as you can get.
>
> The only exception may be toasted fields, which would only apply if you
> had fields with more than 2K of data or so...
>
> Hope this helps,
>
> On Fri, Oct 29, 2004 at 12:24:35PM -0700, Ben wrote:
> > I'm designing a system where I'll be making frequent updates to rows, but
> > some columns will change far less frequently than others. All columns will
> > be read with equal frequency, though probably by means of a materialized
> > view. Updates will happen via a stored proc.
> >
> > Which makes the most sense?
> >
> > 1. Blindly overwrite the value for all columns, even columns that haven't
> > changed.
> >
> > 2. Compare the current value and update each column at a time, leaving
> > unchanged columns alone.
> >
> > 3. Break out my tables more, so that infrequently updated columns are in
> > different tables.
> >
> >
> > I'm thinking that 1 and 2 are similar and less complex than 3, but might
> > result in more dead tuples. 2 and 3 will have to look up values before
> > updates can happen. I don't know if 2 has any advantage over 1. 1 is
> > certainly the simpliest.
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/docs/faqs/FAQ.html
>
> --
> Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> > tool for doing 5% of the work and then sitting around waiting for someone
> > else to do the other 95% so you can sue them.
>