From: ow <oneway_111@yahoo.com>
To: pgsql-sql@postgresql.org
Subject: FK Constraints, indexes and performance
Date: Sun, 5 Oct 2003 17:29:59 -0700 (PDT)
Message-ID: <20031006002959.13352.qmail@web21401.mail.yahoo.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: oneway_111@yahoo.com
Subject: Re: FK Constraints, indexes and performance
In-Reply-To: <20031006002959.13352.qmail@web21401.mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox