Re: Deleting vs foreign keys

On Tue, Oct 25, 2005 at 12:18:34PM +0300, WireSpot wrote:
> On 10/25/05, Michael Fuhr <mike(at)fuhr(dot)org> wrote:
> > Are there indexes on the foreign key columns? That is, given
> > the following example,
> >
> > CREATE TABLE foo (id integer PRIMARY KEY);
> > CREATE TABLE bar (fooid integer NOT NULL REFERENCES foo ON DELETE CASCADE);
> >
> > do you have an index on bar.fooid?
>
> There are no indexes on the referring fields in any tables. Would this
> make a huge difference?

If bar is at all large, yes. Consider if you delete something from foo.
The system has to then scan bar to find all matching fooids. If there
is no index on that column it will do a seq scan. If you delete
everything from foo, it will (sequentially) scan bar once for every row
in foo...

BTW, have you considered TRUNCATE? (although it may not work with
foreign keys).

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.