On Fri, 15 Aug 2003, Christopher Kings-Lynne wrote:
> > > I can also attest to the horrendously long time it takes to restore the
> ADD
> > > FOREIGN KEY section...
> >
> > That really needs to be rewritten to do a single check over the table
> > rather than running the constraint for every row. I keep meaning to get
> > around to it and never actually do. :( I'm not sure that in practice
> > you'll get a better plan at restore time depending on what the default
> > statistics give you.
>
> Surely in the default case it would reduce to using the new hashed IN()
> feature, so it'd be a lot faster?
If we wrote the query using IN that'd be the hope (I've not played with it
enough to guarantee that)
However, on a simple test comparing
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)
and
select * from fk where key in (select key from pk) and key is not null
on a pk table with 100k rows and fk table with 1m rows gives me a
difference of about 2x on my machine.
But that's with a single column int4 key, I haven't tried multi-column
keys or larger key types.
