From: | Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> |
---|---|
To: | Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au> |
Cc: | Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>, Peter Childs <Blue(dot)Dragon(at)blueyonder(dot)co(dot)uk>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: [GENERAL] 7.4Beta |
Date: | 2003-08-15 06:12:01 |
Message-ID: | 20030814223830.O6635-100000@megazone.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Stephan Szabo | 2003-08-15 06:16:50 | Re: [GENERAL] 7.4Beta |
Previous Message | Gavin Sherry | 2003-08-15 06:05:17 | Re: [GENERAL] 7.4Beta |
From | Date | Subject | |
---|---|---|---|
Next Message | Stephan Szabo | 2003-08-15 06:16:50 | Re: [GENERAL] 7.4Beta |
Previous Message | Gavin Sherry | 2003-08-15 06:05:17 | Re: [GENERAL] 7.4Beta |