Re: Slow deletion of data from tables

On Fri, 2004-07-02 at 04:35, Rune Froysa wrote:
> I have one table with columns that are used as foreign-keys from
> several other tables. Sometimes deletion from this table takes +5
> seconds for a single row.

Do you have indexes on the foreign key columns in the child tables?

For example, say you have a person table with primary key person_id and
a hobby table where each row uses person_id as a foreign key.

When you create the person table and declare person_id as the primary
key, PostgreSQL will automatically create a unique index on that column.

When you create the hobby table and declare its person_id references
person_id in the person table then PostgreSQL automatically installs
triggers on the hobby table to ensure updates meet the constraint *and*
it installs triggers on the person table to ensure updates there don't
make records in the hobby table invalid. But no extra indexes will be
created.

If you delete from the person table, a trigger will fire and run a query
something like this:

SELECT 1 FROM ONLY hobby x WHERE person_id = $1 FOR UPDATE OF x

If you don't have a (non-unique) index on the person_id column in the
hobby table then this query will require a full table scan.

So, as a rule of thumb, if a table contains a foreign key, you should
create a non-unique index on that column. There may be good reasons not
to bother in certain cases, but it's a good starting point.

Regards
Grant