Re: Help tuning postgres

On 10/12/05, Csaba Nagy <nagy(at)ecircle-ag(dot)com> wrote:

> We have adapted our application (originally written for oracle) to
> postgres, and switched part of our business to a postgres data base.

> The data base has in the main tables around 150 million rows, the whole
> data set takes ~ 30G after the initial migration. After ~ a month of
> usage that bloated to ~ 100G. We installed autovacuum after ~ 2 weeks.
>
> The main table is heavily updated during the active periods of usage,
> which is coming in bursts.
>
> Now Oracle on the same hardware has no problems handling it (the load),
> but postgres comes to a crawl. Examining the pg_stats_activity table I
> see the updates on the main table as being the biggest problem, they are
> very slow. The table has a few indexes on it, I wonder if they are
> updated too on an update ? The index fields are not changing. In any
> case, I can't explain why the updates are so much slower on postgres.

I'm not the most experience person on this list, but I've got some big
tables I work with. Doing an update on these big tables often involves
a sequential scan which can be quite slow.

I would suggest posting the explain analyze output for one of your
slow updates. I'll bet it is much more revealing and takes out a lot
of the guesswork.

--
Matthew Nuzum
www.bearfruit.org