On Fri, May 06, 2016 at 09:13:09AM -0500, Steven Lembark wrote:
>
> > > It's kind of annoying that I would need to drop the indexes that
> > > aren't modified just to run an update query.
> >
> > I dropped all the index except for the primary key. It was still
> > as slow when it started, but then I forced the primary key into
> > the filesystem cache and it seems to be much happier now, average
> > reading at about 10 MB/s, writing at 30 MB/s.
It finished in 2 hours.
> Look at the PG tuning doc's.
> It seems as if you have too little index cache assigned for the
> size of your database (and its indexes). Dropping indexes isn't
> the real answer, tuning the database to accomodate them is a
> better bet.
As far as I know, the only way to improve this is to make the
shared_buffers larger. But the database is multiple times the
total RAM. Even that table itself is, and all the combined indexes
are too.
And I do agree that dropping the indexes isn't a good idea, but it
seems to be the only way to get it done in a reasonable time.
> It would also be worth checking whether the I/O was entirely due
> to sequential reads or may have been swapping. procinfo, vmstat,
> or just top can tell you about that.
There was no swapping.
The point is that it wasn't doing sequential reads but was
randomly accessing indexes for what looks like no good reason to
me.
Kurt