Re: Very slow update / hash join - Mailing list pgsql-general

From Steven Lembark
Subject Re: Very slow update / hash join
Date
Msg-id 20160506091309.09db8625@cannibal
Whole thread Raw
In response to Re: Very slow update / hash join  (Kurt Roeckx <kurt@roeckx.be>)
Responses Re: Very slow update / hash join
List pgsql-general
> > 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.

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.

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.

--
Steven Lembark                                             3646 Flora Pl
Workhorse Computing                                   St Louis, MO 63110
lembark@wrkhors.com                                      +1 888 359 3508


pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: Debian and Postgres
Next
From: Kurt Roeckx
Date:
Subject: Re: Very slow update / hash join