Re: FK check will use index on referring table?

Stephen Frost <sfrost(at)snowman(dot)net> writes:
> * John D. Burger (john(at)mitre(dot)org) wrote:
>> 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?

There isn't any very good way at the moment :-(

> If more than a few percent of the tokenizedSegments table has a
> segmentId of 24305259 then PG may rightly be scanning the whole table
> sequantially because going through it randomly with an index would be
> slower. There's a few options which can tune those parameters in the
> planner, of course, but you might consider doing a test
> 'set enable_seqscan = false;' first, if it's indeed doing one, to see
> what the difference really is.

One thing to keep in mind while experimenting is that the plan for an FK
update query is cached the first time the particular trigger is fired in
a session; and in 8.2 I don't think there's any way to un-cache it short
of starting a fresh session. This won't affect manual experimentation
of course, but if you do something that you are hoping will change the
trigger's behavior (like fooling with enable_seqscan), be sure to start
a new session first.

Also, the trigger's internal FK query will be parameterized; so the
closest manual equivalent will be something like

prepare foo(int) as delete from tokenizedSegments where segmentId = $1;
explain execute foo(24305259);

(adjust datatype of parameter to match segments.segmentID...) and here
again remember that "prepare" caches the plan.

regards, tom lane