Thread: Slow deletion of data from tables

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.  Are there any utilities that can be used to
figure out why the deletion takes so long?  "ANALYZE DELETE FROM foo
WHERE bar=gazonk" doesn't really help as it only explains how the
where statement is resolved, and not what postgres has to do to
preserve database integrity.

Regards,

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

Grant McLean <grant@catalyst.net.nz> writes:
> 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?

Also: if you do have indexes on the referenced columns, they may still
not get used because of datatype mismatches.  It's generally a good idea
to make sure that referenced and referencing columns of a foreign-key
constraint have exactly the same datatype.

            regards, tom lane