FK Constraints, indexes and performance

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