Re: ADD FOREIGN KEY (was Re: [GENERAL] 7.4Beta)

Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> writes:
> On Sat, 27 Sep 2003, Tom Lane wrote:
>> I thought of what seems to be a better design for the check query: use
>> a LEFT JOIN and check for NULL in the righthand joined column.

> Hmm, my initial testing showed that it really was a little slower
> than a more complicated one with NOT EXISTS so I'd abandoned it. How does
> it fare for you compared to:
> select f1, f2 from fk where not exists (select 1 from pk where pk.f1=fk.f1
> and pk.f2=pk.f2) where fk.f1 is not null and fk.f2 is not null;

Were you testing against 7.3 or 7.4? On what kinds of tables?

In 7.4 I think that the JOIN would yield as good or better a plan. The
best possible plan for the NOT EXISTS query is effectively a nestloop
with inner indexscan, which is great if the FK table is small and the
PK table is large, but it sucks otherwise. The planner should choose a
plan of this form for the LEFT JOIN given that combination of table
sizes, and so there shouldn't be any great difference in runtime in that
case. But in other combinations, such as large FK and small PK, other
plan types will beat the pants off nestloop.

> I've actually got code (that no longer cleanly applies, but...) that uses
> the single query version with NOT EXISTS (which could be easily changed to
> either of the other forms) and was planning to put it together for a patch
> when 7.5 devel started because I figured it wasn't precisely a bug and
> wouldn't get accepted for 7.4.

Well, Bruce has this on his open-items list, so I figure we have a green
light to do something for 7.4 if we can work out what to do.

regards, tom lane