pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feeddrastic reduction in speed of inserts as the table grows
4+ messages / 2 participants
[nested] [flat]
* drastic reduction in speed of inserts as the table grows
@ 2001-04-17 14:28 Rini Dutta <rinid@rocketmail.com>
2001-04-17 15:21 ` Re: drastic reduction in speed of inserts as the table grows Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Rini Dutta @ 2001-04-17 14:28 UTC (permalink / raw)
To: pgsql-sql@postgresql.org; +Cc: pgsql-general@postgresql.org; pgsql-interfaces
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/
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: drastic reduction in speed of inserts as the table grows
2001-04-17 14:28 drastic reduction in speed of inserts as the table grows Rini Dutta <rinid@rocketmail.com>
@ 2001-04-17 15:21 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-04-17 15:32 ` Re: drastic reduction in speed of inserts as the table grows Rini Dutta <rinid@rocketmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2001-04-17 15:21 UTC (permalink / raw)
To: Rini Dutta <rinid@rocketmail.com>; +Cc: pgsql-sql@postgresql.org; pgsql-general@postgresql.org; pgsql-interfaces
Rini Dutta <rinid@rocketmail.com> writes:
> Here is the performance statistics on the same table.
> Note the fall in performance as the test proceeds.
Try 7.1. I think you are running into the lots-of-pending-triggers
problem that was found and fixed awhile back.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: drastic reduction in speed of inserts as the table grows
2001-04-17 14:28 drastic reduction in speed of inserts as the table grows Rini Dutta <rinid@rocketmail.com>
2001-04-17 15:21 ` Re: drastic reduction in speed of inserts as the table grows Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-04-17 15:32 ` Rini Dutta <rinid@rocketmail.com>
2001-04-17 15:33 ` Re: drastic reduction in speed of inserts as the table grows Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Rini Dutta @ 2001-04-17 15:32 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql@postgresql.org; pgsql-general@postgresql.org; pgsql-interfaces
--- Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Rini Dutta <rinid@rocketmail.com> writes:
> > Here is the performance statistics on the same
> table.
> > Note the fall in performance as the test proceeds.
>
> Try 7.1. I think you are running into the
> lots-of-pending-triggers
> problem that was found and fixed awhile back.
>
> regards, tom lane
I'll try it out. Just for my understanding, is the
'lots-of-pending-triggers' problem related to indexes,
or to foreign keys ?
Rini
__________________________________________________
Do You Yahoo!?
Yahoo! Auctions - buy the things you want at great prices
http://auctions.yahoo.com/
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: drastic reduction in speed of inserts as the table grows
2001-04-17 14:28 drastic reduction in speed of inserts as the table grows Rini Dutta <rinid@rocketmail.com>
2001-04-17 15:21 ` Re: drastic reduction in speed of inserts as the table grows Tom Lane <tgl@sss.pgh.pa.us>
2001-04-17 15:32 ` Re: drastic reduction in speed of inserts as the table grows Rini Dutta <rinid@rocketmail.com>
@ 2001-04-17 15:33 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2001-04-17 15:33 UTC (permalink / raw)
To: Rini Dutta <rinid@rocketmail.com>; +Cc: pgsql-sql@postgresql.org; pgsql-general@postgresql.org; pgsql-interfaces
Rini Dutta <rinid@rocketmail.com> writes:
> I'll try it out. Just for my understanding, is the
> 'lots-of-pending-triggers' problem related to indexes,
> or to foreign keys ?
Foreign keys. Does your speed problem go away if the table being
inserted into has no foreign keys?
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2001-04-17 15:33 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-04-17 14:28 drastic reduction in speed of inserts as the table grows Rini Dutta <rinid@rocketmail.com>
2001-04-17 15:21 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-04-17 15:32 ` Rini Dutta <rinid@rocketmail.com>
2001-04-17 15:33 ` Tom Lane <tgl@sss.pgh.pa.us>
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