Re: Strange delete behaviour

On Mon, Aug 01, 2005 at 04:02:14PM +0200, Renzo Kottmann wrote:
> > Do other tables have foreign key references to t_node? If so, are
> > there indexes on those tables' foreign key columns? How many records
> > are in t_node and any tables that reference it? Do you keep the
> > tables vacuumed and analyzed?
>
> Yes. I vacuumed and analyezed. There are several references (t_annotation
> has two references to t_node): Here is the dicription of the tables.

The description for t_annotation shows the two references to t_node
but no indexes on the referencing columns (ann_startnode_id and
ann_endnode_id). When you delete records from t_node, the database
has to check whether those deletions would cause a foreign key
violation, so it has to search t_annotation for matching foreign
keys. Without indexes on the referencing columns, the planner has
to use a sequential scan instead of considering an index scan, so
those searches are likely to be slow. Try creating indexes on the
referencing columns (ann_startnode_id and ann_endnode_id) and on
any other columns that refer to other tables.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/