Re: Transactions vs speed.

* mlw <markw(at)mohawksoft(dot)com> [010113 19:37] wrote:
> Alfred Perlstein wrote:
> >
> > * mlw <markw(at)mohawksoft(dot)com> [010113 17:19] wrote:
> > > I have a question about Postgres:
> > >
> > > Take this update:
> > > update table set field = 'X' ;
> > >
> > >
> > > This is a very expensive function when the table has millions of rows,
> > > it takes over an hour. If I dump the database, and process the data with
> > > perl, then reload the data, it takes minutes. Most of the time is used
> > > creating indexes.
> > >
> > > I am not asking for a feature, I am just musing.
> >
> > Well you really haven't said if you've tuned your database at all, the
> > way postgresql ships by default it doesn't use a very large shared memory
> > segment, also all the writing (at least in 7.0.x) is done syncronously.
> >
> > There's a boatload of email out there that explains various ways to tune
> > the system. Here's some of the flags that I use:
> >
> > -B 32768 # uses over 300megs of shared memory
> > -o "-F" # tells database not to call fsync on each update
>
> I have a good number of buffers (Not 32768, but a few), I have the "-F"
> option.

Explain a "good number of buffers" :)

Also, when was the last time you ran vacuum on this database?

--
-Alfred Perlstein - [bright(at)wintelcom(dot)net|alfred(at)freebsd(dot)org]
"I have the heart of a child; I keep it in a jar on my desk."