Re: FK Constraints, indexes and performance - Mailing list pgsql-sql

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