Re: Rapidly decaying performance repopulating a large table

On Tue, Apr 22, 2008 at 3:15 PM, David Wilson <david.t.wilson@gmail.com> wrote:
> On Tue, Apr 22, 2008 at 5:04 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
>  >  Normally, after the first 50,000 or so the plan won't likely change
>  >  due to a new analyze, so you could probably just analyze after 50k or
>  >  so and get the same performance.  If the problem is a bad plan for the
>  >  inserts / copies.
>  >
>  >  also, non-indexed foreign keyed fields can cause this problem.
>  >
>
>  Analyzing after the first 50k or so is easy enough, then; thanks for
>  the suggestion.
>
>  Foreign keys are definitely indexed (actually referencing a set of
>  columns that the foreign table is UNIQUE on).
>
>  Any other suggestions? COPY times alone are pretty much quadrupling my
>  table-rebuild runtime, and I can interrupt the current rebuild to try
>  things pretty much at a whim (nothing else uses the DB while a rebuild
>  is happening), so I'm pretty much game to try any reasonable
>  suggestions anyone has.

Try upping your checkpoint segments.  Some folks find fairly large
numbers like 50 to 100 to be helpful.  Each segment = 16Megs, so be
sure not to run your system out of drive space while increasing it.