Re: Deleting vs foreign keys

On Tue, Oct 25, 2005 at 12:59:27AM +0300, WireSpot wrote:
> I have an application that makes heavy use of foreign keys all over
> the tables. This is very nice since the data is very consistent. There
> also this "central" table which holds "sites" in it. A site pretty
> much is the crux of it all. Deleting a site will very precisely
> eliminate all data regarding it, since there's CASCADE on delete's
> everywhere.
>
> The only trouble I'm having is that the original developers apparently
> didn't account for large amounts of data. I'm starting to get a LOT of
> data in some tables, and nowadays deleting a site will take a
> disgusting amount of time (in the range of tens of minutes).

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? Also, do you regularly vacuum
and analyze the database?

--
Michael Fuhr