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
