On Fri, 15 Aug 2003, Tom Lane wrote:
> Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> > select * from fk where not exists(select * from pk where pk.key=fk.key)
> > and key is not null;
> > (doing seq scan/subplan doing index scan - which is probably close to the
> > current system)
>
> Actually, even that would probably be noticeably better than the current
> system. I haven't profiled it (someone should) but I suspect that
> executor startup/shutdown time is a huge hit. Even though the trigger
> is caching a plan, it has to instantiate that plan for each referencing
> tuple --- and the executor is not designed for quick startup/shutdown.
Yeah, but it was pretty much the best I could do testing on the command
line. And it was still a fair bit more expensive than using IN (my tests
on various key types showed anywhere from 15% to 300% better speed on IN
over exists for this).