Thread: FK check will use index on referring table?

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

* John D. Burger (john@mitre.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?

It should, when it makes sense, yes.  Having the actual schema
definitions would help in debugging this, of course.

> EXPLAIN ANALYZE just shows something like this:
>
> => explain analyze delete from segments where segmentid = 24305259;

What does:

explain analyze
delete from tokenizedSegments
where segmentId = 24305259;

look like?

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.

    Thanks,

        Stephen

Stephen Frost <sfrost@snowman.net> writes:
> * John D. Burger (john@mitre.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

On Thu, Jul 24, 2008 at 7:06 PM, Stephen Frost <sfrost@snowman.net> wrote:
> * John D. Burger (john@mitre.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?
>
> It should, when it makes sense, yes.  Having the actual schema
> definitions would help in debugging this, of course.
>
>> EXPLAIN ANALYZE just shows something like this:
>>
>> => explain analyze delete from segments where segmentid = 24305259;
>
> What does:
>
> explain analyze
> delete from tokenizedSegments
> where segmentId = 24305259;
>
> look like?

If you're doing that put it in a begin; rollback; sandwich so your
rows don't disappear while testing.

Or just do a

explain analyze select 1 from tokenizedSegments where segentId=23405259;

the plan would be the same I'd think

>
> 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.
>
>        Thanks,
>
>                Stephen
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.9 (GNU/Linux)
>
> iEYEARECAAYFAkiJJxIACgkQrzgMPqB3kighnACfd1AaKusTxFaKIqcqEjAmvRwm
> LmwAnR0YegtP/rr84LiVVAMJUv3dYOMj
> =dPu1
> -----END PGP SIGNATURE-----
>
>