pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFK Constraints, indexes and performance
4+ messages / 3 participants
[nested] [flat]
* FK Constraints, indexes and performance
@ 2003-10-06 00:29 ow <oneway_111@yahoo.com>
2003-10-06 04:40 ` Re: FK Constraints, indexes and performance Tom Lane <tgl@sss.pgh.pa.us>
2003-10-06 04:59 ` Re: FK Constraints, indexes and performance Peter Childs <Blue.Dragon@blueyonder.co.uk>
0 siblings, 2 replies; 4+ messages in thread
From: ow @ 2003-10-06 00:29 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: FK Constraints, indexes and performance
2003-10-06 00:29 FK Constraints, indexes and performance ow <oneway_111@yahoo.com>
@ 2003-10-06 04:40 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-10-07 00:37 ` Re: FK Constraints, indexes and performance ow <oneway_111@yahoo.com>
1 sibling, 1 reply; 4+ messages in thread
From: Tom Lane @ 2003-10-06 04:40 UTC (permalink / raw)
To: ow <oneway_111@yahoo.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: FK Constraints, indexes and performance
2003-10-06 00:29 FK Constraints, indexes and performance ow <oneway_111@yahoo.com>
2003-10-06 04:40 ` Re: FK Constraints, indexes and performance Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-10-07 00:37 ` ow <oneway_111@yahoo.com>
0 siblings, 0 replies; 4+ messages in thread
From: ow @ 2003-10-07 00:37 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
--- 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: FK Constraints, indexes and performance
2003-10-06 00:29 FK Constraints, indexes and performance ow <oneway_111@yahoo.com>
@ 2003-10-06 04:59 ` Peter Childs <Blue.Dragon@blueyonder.co.uk>
1 sibling, 0 replies; 4+ messages in thread
From: Peter Childs @ 2003-10-06 04:59 UTC (permalink / raw)
To: ow <oneway_111@yahoo.com>; 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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-10-07 00:37 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-10-06 00:29 FK Constraints, indexes and performance ow <oneway_111@yahoo.com>
2003-10-06 04:40 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-10-07 00:37 ` ow <oneway_111@yahoo.com>
2003-10-06 04:59 ` Peter Childs <Blue.Dragon@blueyonder.co.uk>
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