Search Postgresql Archives

Re: FK check will use index on referring table?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Thu, Jul 24, 2008 at 7:06 PM, Stephen Frost <sfrost@xxxxxxxxxxx> wrote:
> * 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?
>
> 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-----
>
>


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux