Re: [SQL] FK Constraints, indexes and performance

[Peter Childs]

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. 1 sequential scans
> against 2 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


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] FK Constraints, indexes and performance

[Tom Lane]

ow <[EMAIL PROTECTED]> 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

---(end of broadcast)---
TIP 8: explain analyze is your friend

[SQL] FK Constraints, indexes and performance

[ow]

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. 1 sequential scans against 2
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