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 ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html