Re: FK check will use index on referring table?

On Thu, Jul 24, 2008 at 7:06 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> * 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?
>
> 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-----
>
>