Re: Poor performance of UPDATE against busy table

In article <20010906141522(dot)A3371(at)rice(dot)edu>, "Ross J. Reedstrom"
<reedstrm(at)rice(dot)edu> wrote:

> On Thu, Sep 06, 2001 at 02:31:51PM -0400, Jeff Boes wrote:
>> Does anyone know if there are ways to improve the performance of UPDATE
>> against a table where there are many readers?

> You don't mention your version of pgsql, but if you get a lot of
> throughput on that table, how often do you VACUUM it? All those dead
> tuples take space that has to be scanned through, since indices don't
> keep track of tuple state.

Bingo, you got it. Actually, I came to this conclusion shortly after I
posted the original note, and did a VACUUM, which improved performance by
a factor of 100.

Excuses:

1) This is my first PostgreSQL database, so I wasn't aware of how
critical VACUUM can be in this kind of situation.

2) It's a development system, not a production system, so we don't have
regular backup/reorg set up yet (but it just jumped to the head of the
list of things-I-need-to-do-yesterday!).

--
Jeff Boes vox 616.226.9550
Database Engineer fax 616.349.9076
Nexcerpt, Inc. jboes(at)nexcerpt(dot)com