Re: How are foreign key constraints built?

On Sun, Jan 23, 2005 at 02:01:41PM -0500, Tom Lane wrote:
> Wes <wespvp@syntegra.com> writes:
> > There's no problem here, I'd just like to understand what it is doing.
>
> Either a hash or merge join between the two tables, to verify that all
> the keys in the referencing table exist in the referenced table.  The
> intermediate data is evidently spilling to disk.

If this were done with a nested loop, you wouldn't need any intermediate
data, right? You can just throw the result-set away. ISTM that in the
case of just verifying existance of data, it might be faster to do a
nested loop that doesn't have to spill anywhere, instead of a hash or
merge that needs to generate a bunch of intermediate data. Is the
optimizer able to take this into account?

> > I expected adding the foreign key constraint would just use the existing
> > indexes to verify the database is currently consistent.
>
> People have this weird notion that an index-based plan is always faster
> than anything else.  If you like you can try the operation with "set
> enable_seqscan = off", but I bet it will take longer.

Well, every other database I've used can do index covering, which means
index scans *are* faster.
--
Jim C. Nasby, Database Consultant               decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"