From: | "Jim C(dot) Nasby" <decibel(at)decibel(dot)org> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Wes <wespvp(at)syntegra(dot)com>, Postgresql-General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: How are foreign key constraints built? |
Date: | 2005-01-23 21:19:10 |
Message-ID: | 20050123211910.GW67721@decibel.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Sun, Jan 23, 2005 at 02:01:41PM -0500, Tom Lane wrote:
> Wes <wespvp(at)syntegra(dot)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(at)decibel(dot)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?"
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2005-01-23 21:45:36 | Re: How are foreign key constraints built? |
Previous Message | Alvaro Herrera | 2005-01-23 21:16:50 | Re: text and varchar |