Re: [PERFORM] 8.x index insert performance

At 09:43 AM 11/14/2005, Kelly Burkhart wrote: On Fri, 2005-11-11 at 18:02 -0500, Tom Lane wrote: > > 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. > > I conjecture that the problem areas represe

Re: [PERFORM] 8.x index insert performance

On Fri, 2005-11-11 at 18:02 -0500, Tom Lane wrote: > > 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. > > I conjecture that the problem areas represent places where the key > sequence is significa

Re: [PERFORM] 8.x index insert performance

Kelly Burkhart <[EMAIL PROTECTED]> writes: > On Thu, 2005-11-10 at 19:13 -0500, Tom Lane wrote: >> 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: > <

Re: [PERFORM] 8.x index insert performance

That sure seems to bolster the theory that performance is degrading because you exhaust the cache space and need to start reading index pages. When inserting sequential data, you don't need to randomly access pages all over the index tree. -Kevin >>> Kelly Burkhart <[EMAIL PROTECTED]> >>> I m

Re: [PERFORM] 8.x index insert performance

On Thu, 2005-11-10 at 19:13 -0500, Tom Lane wrote: > Kelly Burkhart <[EMAIL PROTECTED]> writes: > > ... A graph showing the performance > > characteristics is here: > > > > > I hadn't looked at this chart till just now, but it sure seems to put a > crimp in my theory

Re: [PERFORM] 8.x index insert performance

Kelly Burkhart <[EMAIL PROTECTED]> writes: > ... A graph showing the performance > characteristics is here: > 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

Re: [PERFORM] 8.x index insert performance

Kelly Burkhart <[EMAIL PROTECTED]> writes: > On Thu, 2005-11-10 at 17:18 -0500, Tom Lane wrote: >> Could you send me (off list) the complete gprof output files? > Sure, Thanks. Right offhand I can see no smoking gun here. The pg_detoast_datum entry I was worried about seems to be just measureme

Re: [PERFORM] 8.x index insert performance

Kelly Burkhart <[EMAIL PROTECTED]> writes: > I've finally gotten around to profiling the back end. Thanks for following up. The sudden appearance of pg_detoast_datum() in the top ten in the third profile is suspicious. I wouldn't expect that to get called at all, really, during a normal COPY IN

Re: [PERFORM] 8.x index insert performance

Second try... no attachment this time. I've finally gotten around to profiling the back end. Here is a more precise description of what I'm doing: I am copying data into two tables, order_main and order_transition (table defs at the end of this post). The order_transition table has roughly doub

Re: [PERFORM] 8.x index insert performance

On Tue, 2005-11-01 at 10:37 -0500, Ron Peacetree wrote: > I'm surprised that no one seems to have yet suggested the following > simple experiment: > > Increase the RAM 4GB -> 8GB, tune for best performance, and > repeat your 100M row insert experiment. > > Does overall insert performance change?

Re: [PERFORM] 8.x index insert performance

"Merlin Moncure" <[EMAIL PROTECTED]> writes: > > select * from sometable where somefield IS NULL won't work because IS > is > > not a nomally indexible operator. > > Ah, I didn't know that. So there is no real reason not to exclude null > values from all your indexes :). Reading Tom's recent co

Re: [PERFORM] 8.x index insert performance

ql-performance@postgresql.org Subject: [PERFORM] 8.x index insert performance Greetings, We are running some performance tests in which we are attempting to insert about 100,000,000 rows in a database at a sustained rate. About 50M rows in, our performance drops dramatically. This test is with data t

Re: [PERFORM] 8.x index insert performance

Kelly Burkhart <[EMAIL PROTECTED]> writes: > 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? Add -g -pg and leave the rest alone. Also, if you're on Linux note that you need -DLINUX_PROFILE. > It may b

Re: [PERFORM] 8.x index insert performance

On Mon, 2005-10-31 at 16:18 -0500, Tom Lane wrote: > Kelly Burkhart <[EMAIL PROTECTED]> 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 in

Re: [PERFORM] 8.x index insert performance

On Mon, Oct 31, 2005 at 03:27:31PM -0500, Merlin Moncure wrote: > > On Mon, Oct 31, 2005 at 12:32:03PM -0500, Merlin Moncure wrote: > > > if that index is causing the problem, you may want to consider setting > > > up partial index to exclude null values. > > Hey all. > > Pardon my ignorance. :-) >

Re: [PERFORM] 8.x index insert performance

Kelly Burkhart <[EMAIL PROTECTED]> 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

Re: [PERFORM] 8.x index insert performance

"Merlin Moncure" <[EMAIL PROTECTED]> writes: >> You're mistaken, at least with regard to btree indexes. > hmm. I tried several different ways to filter/extract null values from > an indexed key and got a seq scan every time. I said they were stored, not that you could query against them ;-) IS NU

Re: [PERFORM] 8.x index insert performance

> select * from sometable where somefield IS NULL won't work because IS is > not a nomally indexible operator. Ah, I didn't know that. So there is no real reason not to exclude null values from all your indexes :). Reading Tom's recent comments everything is clear now. Instead of using your two

Re: [PERFORM] 8.x index insert performance

On Mon, 2005-10-31 at 13:13, Merlin Moncure wrote: > > > if that index is causing the problem, you may want to consider > setting > > > up partial index to exclude null values. > > > > This is a single column index. I assumed that null column values were > > not indexed. Is my assumption incorre

Re: [PERFORM] 8.x index insert performance

> [EMAIL PROTECTED] writes: > > I've been trying to figure out whether null values are indexed or not > from > > the documentation. I was under the impression, that null values are not > > stored in the index. > > You're mistaken, at least with regard to btree indexes. hmm. I tried several differ

Re: [PERFORM] 8.x index insert performance

On Mon, 2005-10-31 at 15:30 -0500, Tom Lane wrote: > [EMAIL PROTECTED] writes: > > I've been trying to figure out whether null values are indexed or not from > > the documentation. I was under the impression, that null values are not > > stored in the index. > > You're mistaken, at least with rega

Re: [PERFORM] 8.x index insert performance

[EMAIL PROTECTED] writes: > I've been trying to figure out whether null values are indexed or not from > the documentation. I was under the impression, that null values are not > stored in the index. You're mistaken, at least with regard to btree indexes. regards, tom lane

Re: [PERFORM] 8.x index insert performance

> On Mon, Oct 31, 2005 at 12:32:03PM -0500, Merlin Moncure wrote: > > if that index is causing the problem, you may want to consider setting > > up partial index to exclude null values. > > Hey all. > > Pardon my ignorance. :-) > > I've been trying to figure out whether null values are indexed o

Re: [PERFORM] 8.x index insert performance

On Mon, Oct 31, 2005 at 12:32:03PM -0500, Merlin Moncure wrote: > if that index is causing the problem, you may want to consider setting > up partial index to exclude null values. Hey all. Pardon my ignorance. :-) I've been trying to figure out whether null values are indexed or not from the doc

Re: [PERFORM] 8.x index insert performance

> > if that index is causing the problem, you may want to consider setting > > up partial index to exclude null values. > > This is a single column index. I assumed that null column values were > not indexed. Is my assumption incorrect? > > -K It turns out it is, or it certainly seems to be. I

Re: [PERFORM] 8.x index insert performance

> We are running some performance tests in which we are attempting to > insert about 100,000,000 rows in a database at a sustained rate. About > 50M rows in, our performance drops dramatically. > > This test is with data that we believe to be close to what we will > encounter in production. Howev

Re: [PERFORM] 8.x index insert performance

On Mon, 2005-10-31 at 12:32 -0500, Merlin Moncure wrote: > well, can you defer index generation until after loading the set (or use > COPY?) I cannot defer index generation. We are using the copy API. Copying 1 rows in a batch. > > if that index is causing the problem, you may want to cons

Re: [PERFORM] 8.x index insert performance

Kelly wrote: > We are running some performance tests in which we are attempting to > insert about 100,000,000 rows in a database at a sustained rate. About > 50M rows in, our performance drops dramatically. > > This test is with data that we believe to be close to what we will > encounter in produ

[PERFORM] 8.x index insert performance

Greetings, We are running some performance tests in which we are attempting to insert about 100,000,000 rows in a database at a sustained rate. About 50M rows in, our performance drops dramatically. This test is with data that we believe to be close to what we will encounter in production. Howe