FK check will use index on referring table?

From: "John D(dot) Burger" <john(at)mitre(dot)org>
To: General PostgreSQL List <pgsql-general(at)postgresql(dot)org>
Subject: FK check will use index on referring table?
Date: 2008-07-24 15:21:23
Message-ID: D3693D05-1AA0-4BA3-BF3B-CB82A41EB7CC@mitre.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi -

My understanding is that PG will use an index on the referring side
of a foreign key for FK checks. How can I tell whether it's doing
that? EXPLAIN ANALYZE just shows something like this:

=> explain analyze delete from segments where segmentid = 24305259;
QUERY PLAN
------------------------------------------------------------------------
-------------------------------------------------
Index Scan using segments_pkey on segments (cost=0.00..10.46
rows=1 width=6)
(actual
time=0.243..0.248 rows=1 loops=1)
Index Cond: (segmentid = 24305259)
Trigger for constraint $1: time=0.344 calls=1
Trigger for constraint $2: time=0.180 calls=1
Trigger for constraint $1: time=0.325 calls=1
Trigger for constraint tokenizedsegments_segmentid_fkey:
time=16910.357 calls=1
Total runtime: 16911.712 ms

tokenizedSegments.segmentID has an FK reference to
segments.segmentID, and there is an index (not UNIQUE) on the
referring column, but the extreme sloth of that last trigger suggests
it is not using it. Deferring doesn't matter (perhaps not surprising
on one delete).

How can I tell what the trigger is doing? I'm using 8.2.5 and I've
ANALYZED everything.

Thanks.

- John D. Burger
MITRE

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Aljosa Mohorovic 2008-07-24 15:30:51 import from ibm db2
Previous Message David Spadea 2008-07-24 15:13:52 Re: php + postgresql