Pg 7.3.3 on i386 compiled by GCC 2.96
Hi,
It's understood that FK constraints carry some performance hit. However, the
performance hit I observe is huge. My situation is illustrated by the table
structures below.
Parent table has 20,000 rows and Child table has about 60,000.
Without fk_child_parentid constraint, it takes about 9 seconds to insert 10,000
records into the Child table. WITH fk_child_parentid constraint, it takes about
300 (!) seconds to insert the same 10,000 into the Child table.
The reason for such poor performace with the fk_child_parentid constraint is
the fact that, I think, when verifying the fk_child_parentid constraint, PG is
doing sequential scan of the Parent table instead of the using the implicit
index created by the pk_parent constraint. 10000 sequential scans against 20000
row table really take a hit on performance.
The reason I think PG is doing sequential scans is because the execution plan
for the following query shows two sequential scans:
explain select *
from parent, child
where child.parentId = parent.id
With reference to the above, two (2) questions:
1) Is there anything that can be done to significantly improve Child insert
performance when fk_child_parentid is in place?
2) Why wouldn't PG use implicit index pk_parent when resolving
"where C.parentId =P.id" in the query above.
Thanks
------------------------------------ Test table structures
Domains
test.did = int
test.dname = varchar(30)
test.dstringlong = varchar(50)
CREATE TABLE test.parent
( id test.did NOT NULL, name test.dname NOT NULL, CONSTRAINT pk_parent PRIMARY KEY (id), CONSTRAINT ak_parent_name
UNIQUE(name)
) WITH OIDS;
CREATE TABLE test.child
( id test.didlong NOT NULL, parentid test.did NOT NULL, name test.dstringlong NOT NULL, CONSTRAINT pk_child PRIMARY KEY
(id),CONSTRAINT fk_child_parentid FOREIGN KEY (parentid) REFERENCES test.parent
(id) ON UPDATE RESTRICT ON DELETE RESTRICT,
) WITH OIDS;
__________________________________
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com
