Re: 8.x index insert performance

On Thu, 2005-11-10 at 19:13 -0500, Tom Lane wrote:
> Kelly Burkhart <kelly(at)tradebotsystems(dot)com> writes:
> > ... A graph showing the performance
> > characteristics is here:
>
> > <http://kkcsm.net/pgcpy.jpg>
>
> I hadn't looked at this chart till just now, but it sure seems to put a
> crimp in my theory that you are running out of room to hold the indexes
> in RAM. That theory would predict that once you fall over the knee of
> the curve, performance would get steadily worse; instead it gets
> markedly worse and then improves a bit. And there's another cycle of
> worse-and-better around 80M rows. I have *no* idea what's up with that.
> Anyone? Kelly, could there be any patterns in the data that might be
> related?

I modified my original program to insert generated, sequential data.
The following graph shows the results to be flat:

<http://kkcsm.net/pgcpy_20051111_1.jpg>

Thus, hardware is sufficient to handle predictably sequential data.
There very well could be a pattern in the data which could affect
things, however, I'm not sure how to identify it in 100K rows out of
100M.

If I could identify a pattern, what could I do about it? Could I do
some kind of a reversible transform on the data? Is it better to insert
nearly random values? Or nearly sequential?

I now have an 8G and a 16G machine I'm loading the data into. I'll
report back after that's done.

I also want to try eliminating the order_main table, moving fields to
the transition table. This will reduce the number of index updates
significantly at the cost of some wasted space in the table...

-K