Обсуждение: 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
ow <oneway_111@yahoo.com> writes: > It's understood that FK constraints carry some performance hit. However, the > performance hit I observe is huge. It looks to me like the 7.3 planner will not choose indexscans for the FK check queries in this example, because the comparison operators are misconstrued as shown in this thread: http://archives.postgresql.org/pgsql-hackers/2003-03/msg00997.php The equality operator on your domain is taken to be "oideq" which won't be the same operator associated with the index on the column. This seems to be fixed in 7.4. In 7.3 I'd counsel not introducing domains unnecessarily. regards, tom lane
On Monday 06 October 2003 01:29, ow wrote: > 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. Forget the idea that there is an index unless you put one on your self. This is because on a relly small parent table a sequencal scan can be faster than a index scan and hence be pointless. esspecally if the table needs updating regularly. So create an index on any relevent fields. Analyse the tables. and see if you get any improvements. You can always remove the indexes again if they don't help.There was talk some time ago of keeping stats on foriegn keys so that they could be used for an extra preformace gain. but a think that may have got on the todo list. Peter Childs > > 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
--- Tom Lane <tgl@sss.pgh.pa.us> wrote: > It looks to me like the 7.3 planner will not choose indexscans for the FK > check queries in this example, because the comparison operators are > misconstrued as shown in this thread: > http://archives.postgresql.org/pgsql-hackers/2003-03/msg00997.php > The equality operator on your domain is taken to be "oideq" which won't > be the same operator associated with the index on the column. > > This seems to be fixed in 7.4. In 7.3 I'd counsel not introducing > domains unnecessarily. > > regards, tom lane It looks like it worked. I moved to 7.4.b4 and, in my case, performance improvement on insert is drastic (about 30x). Thanks __________________________________ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com