Re: 8.x index insert performance

On Mon, 2005-10-31 at 16:18 -0500, Tom Lane wrote:
> Kelly Burkhart <kelly(at)tradebotsystems(dot)com> writes:
> > Ha! So I'm creating an index 98% full of nulls! Looks like this is
> > easily fixed with partial indexes.
>
> Still, though, it's not immediately clear why you'd be seeing a severe
> dropoff in insert performance after 50M rows. Even though there are
> lots of nulls, I don't see why they'd behave any worse for insert speed
> than real data. One would like to think that the insert speed would
> follow a nice O(log N) rule.
>
> Are you doing the inserts all in one transaction, or several? If
> several, could you get a gprof profile of inserting the same number of
> rows (say a million or so) both before and after the unexpected dropoff
> occurs?

I'm doing the inserts via libpq copy. Commits are in batches of approx
15000 rows. I did a run last night after modifying the indexes and saw
the same pattern. I'm dumping the database now and will modify my test
program to copy data from the dump rather than purely generated data.
Hopefully, this will allow me to reproduce the problem in a way that
takes less time to set up and run.

Tom, I'd be happy to profile the backend at several points in the run if
you think that would be helpful. What compiler flags should I use?
Current settings in Makefile.global are:

CFLAGS = -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline
-Wendif-labels -fno-strict-aliasing

Should I change this to:

CFLAGS = -g -pg -Wall ...

Or should I leave the -O2 in?

It may be weekend by the time I get this done.

-K