From: Rini Dutta <rinid@rocketmail.com>
To: pgsql-sql@postgresql.org
Cc: pgsql-general@postgresql.org
Cc: pgsql-interfaces@postgresql.org
Subject: drastic reduction in speed of inserts as the table grows
Date: Tue, 17 Apr 2001 07:28:53 -0700 (PDT)
Message-ID: <20010417142853.64642.qmail@web12507.mail.yahoo.com> (raw)
Hi,
I was comparing speed of inserts in C vs JDBC and
found that as the table size increases the speed
differential decreases till there is no difference (no
problem). However inserts kept getting slower and
slower as the table size increased and the performance
became quite poor. Here is the data including the
table descriptions -
CREATE TABLE some_table(
idx serial,
a_idx int4 NOT NULL,
b_idx int4 NOT NULL,
c_address varchar(20) NOT NULL,
d_address varchar(20) NOT NULL,
PRIMARY KEY(idx),
CONSTRAINT a_fkey1 FOREIGN KEY(a_idx)
REFERENCES a_ref(idx),
CONSTRAINT b_fkey2 FOREIGN KEY(b_idx)
REFERENCES b_ref(idx)
);
CREATE INDEX some_index on some_table (a_idx, b_idx,
c_address, d_address);
Here is the performance statistics on the same table.
Note the fall in performance as the test proceeds.
# of inserts C (in sec) JDBC (in sec)
(as 1 transaction)
500 1 1.7
1000 3 3.4
2000 6 7.5
another 6000 inserts ...
then
10000 70.8
10000 283
(ran vacuum at this point to see if it helped)
10000 355
1000 36 37
100 3.8 3.8
I ran these tests on a Linux machine (299 MHz). I used
postgres v7.0.3 but then I even tried grouping a large
number of inserts in one transaction to reduce the
number of hard-disk writes (it did not make a
difference as shown in the above data)
I am concerned about the drastic fall in performance
with increase of table size. Is this expected behavior
? Would this be related to indexes existing on the
table? I would expect indexes to make inserts slower
but I do not see how it explains such a great fall in
performance with increasing table-size. Is there a way
to avoid this drop in performance ?
Thanks,
Rini
__________________________________________________
Do You Yahoo!?
Yahoo! Auctions - buy the things you want at great prices
http://auctions.yahoo.com/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: rinid@rocketmail.com, pgsql-sql@postgresql.org, pgsql-general@postgresql.org
Subject: Re: drastic reduction in speed of inserts as the table grows
In-Reply-To: <20010417142853.64642.qmail@web12507.mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox