On Sun, Jan 23, 2005 at 02:01:41PM -0500, Tom Lane wrote: > Wes <wespvp@xxxxxxxxxxxx> 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@xxxxxxxxxxx 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?" ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
