Stephen Frost <sfrost@xxxxxxxxxxx> writes: > * John D. Burger (john@xxxxxxxxx) 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
