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/
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
