Message-ID: <20031006002959.13352.qmail@web21401.mail.yahoo.com>
Date: Sun, 5 Oct 2003 17:29:59 -0700 (PDT)
From: ow <oneway_111@yahoo.com>
Subject: FK Constraints, indexes and performance
To: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

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