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 significantly
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 represent
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:
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
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 modified
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:
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? Does
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
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
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 measurement
Kelly Burkhart [EMAIL PROTECTED] 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
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 comments
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 insert
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 be
@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 that we believe
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.
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
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
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. However in
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 didn't
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
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
[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
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 regard to
[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 different ways
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 incorrect?
-K
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
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 NULL
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
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. :-)
I've been
29 matches
Mail list logo
