Re: Rapidly decaying performance repopulating a large table

On Tue, Apr 22, 2008 at 6:10 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "David Wilson" <david(dot)t(dot)wilson(at)gmail(dot)com> writes:
>
> Are you loading any tables that are the targets of foreign key
> references from other tables being loaded? If so, I'd bet on
> Scott's theory being correct with respect to the plans for checks
> of those FK constraints. The COPY itself hasn't got any plan,
> and inserting rows into a table should be constant-time in itself,
> so it seems to me there are only two possibilities for a progressive
> slowdown:
>
> * the cost of updating the indexes, which for standard btree indexes
> ought to grow at about log(N) when there are already N entries
>
> * bad plans in either foreign-key triggers or user-defined triggers
> attached to the tables.

Only one table is being regenerated, and it's not the target of any
foreign key checks itself; it merely has a single FK reference out to
one unchanging table. There are no triggers on the table.

> You failed to mention what PG version this is (tut tut) but if it's
> less than 8.3 then ANALYZE alone won't fix bad plans in triggers;
> you'd need to analyze and then start a fresh database session.

PG is 8.3.1.

I certainly expect some slowdown, given that I have indices that I
can't drop (as you indicate above). Having been watching it now for a
bit, I believe that the checkpoint settings were the major cause of
the problem, however. Changing those settings has dropped the copy
times back down toward what I'd expect; I have also now dropped the FK
constraint, but that has made no perceptible difference in time.

My guess at this point is that I'm just running into index update
times and checkpoint IO. The only thing that still seems strange is
the highly variable nature of the COPY times- anywhere from <1.0
seconds to >20 seconds, with an average probably around 8ish. I can
live with that, but I'm still open to any other suggestions anyone
has!

Thanks for the help so far.

--
- David T. Wilson
david(dot)t(dot)wilson(at)gmail(dot)com