Обсуждение: Strange delete behaviour

Hello,

I have a strange delete behaviour in my postgres 8.0.3 database:

If I try a

delete
     from   t_node
     where  node_doc_id = XX;

from inside a plpgsql function

on this table:

CREATE TABLE  t_node  (
    node_global_id     int4 DEFAULT nextval('seq_node')  NOT NULL ,
    node_doc_id        int4 NOT NULL ,
    node_local_id      int4 NOT NULL ,
    node_offset        int4 NOT NULL ,
   FOREIGN KEY ( node_doc_id )
      REFERENCES  t_document ( doc_id )
      MATCH FULL ,
   PRIMARY KEY ( node_global_id )
);

CREATE UNIQUE INDEX idx_node ON t_node (
    node_doc_id
    node_local_id
);

The deletion does not finish after several minutes and the CPU is
running at 100% all the time unless I stop postmaster. A select works
normal and gives me around 2500 rows. Does anybody has an idea why this
happens?

Thanks,
renzo

Gnanavel S wrote:
> post the description of the t_node and t_document tables for more
> information
>
> On 8/1/05, Renzo Kottmann <renzo@tzi.de> wrote:
>
>>Hello,
>>
>>I have a strange delete behaviour in my postgres 8.0.3 database:
>>
>>If I try a
>>
>>delete
>>from t_node
>>where node_doc_id = XX;
>>
>>from inside a plpgsql function
>>
>>on this table:
>>
>>CREATE TABLE t_node (
>>node_global_id int4 DEFAULT nextval('seq_node') NOT NULL ,
>>node_doc_id int4 NOT NULL ,
>>node_local_id int4 NOT NULL ,
>>node_offset int4 NOT NULL ,
>>FOREIGN KEY ( node_doc_id )
>>REFERENCES t_document ( doc_id )
>>MATCH FULL ,
>>PRIMARY KEY ( node_global_id )
>>);
>>
>>CREATE UNIQUE INDEX idx_node ON t_node (
>>node_doc_id
>>node_local_id
>>);
>>
>>The deletion does not finish after several minutes and the CPU is
>>running at 100% all the time unless I stop postmaster. A select works
>>normal and gives me around 2500 rows. Does anybody has an idea why this
>>happens?
>>
>>Thanks,
>>renzo


t_node see above and in addition t_document:


CREATE TABLE  t_document  (
    doc_id             int4 DEFAULT nextval('seq_document')  NOT NULL ,
    doc_content_id     int4,
    doc_lr_id          int4 NOT NULL ,
    doc_url            text NULL ,
    doc_start          int4,
    doc_end            int4,
    doc_is_markup_aware  bool NOT NULL ,
   FOREIGN KEY ( doc_content_id )
      REFERENCES  t_doc_content ( dc_id )
      MATCH FULL ,
   FOREIGN KEY ( doc_lr_id )
      REFERENCES  t_lang_resource ( lr_id )
      MATCH FULL ,
   PRIMARY KEY ( doc_id )
);
CREATE UNIQUE INDEX xt_document_01 on  t_document (doc_lr_id);

On Mon, Aug 01, 2005 at 01:57:32PM +0200, Renzo Kottmann wrote:
> If I try a
>
> delete
>      from   t_node
>      where  node_doc_id = XX;
>
> from inside a plpgsql function
> ...
> The deletion does not finish after several minutes and the CPU is
> running at 100% all the time unless I stop postmaster. A select works
> normal and gives me around 2500 rows. Does anybody has an idea why this
> happens?

What happens if you execute the delete by itself, i.e., not from
inside a function?  What output do you get if you connect to the
database with psql and execute "EXPLAIN ANALYZE DELETE ..."?

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?

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

> On Mon, Aug 01, 2005 at 01:57:32PM +0200, Renzo Kottmann wrote:
>> If I try a
>>
>> delete
>>      from   t_node
>>      where  node_doc_id = XX;
>>
>> from inside a plpgsql function
>> ...
>> The deletion does not finish after several minutes and the CPU is
>> running at 100% all the time unless I stop postmaster. A select works
>> normal and gives me around 2500 rows. Does anybody has an idea why this
>> happens?
>
> What happens if you execute the delete by itself, i.e., not from
> inside a function?

The same! Before I did "delete from   t_node where  node_doc_id = XX;"

I did

1. "delete from  t_as_annotation where  asann_ann_id in (select
ann_global_id from   t_annotation  where  ann_doc_id = XX);"

2. "delete from t_annotation where ann_doc_id = XX;"

3. "delete from t_annot_set where as_doc_id = XX;"

These are the same statements in the same order like in the function.

> What output do you get if you connect to the
> database with psql and execute "EXPLAIN ANALYZE DELETE ..."?

It also hangs up with 100% CPU load.

> 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.

                         Table "public.t_node"
     Column     |  Type   |                 Modifiers
----------------+---------+--------------------------------------------
 node_global_id | integer | not null default nextval('seq_node'::text)
 node_doc_id    | integer | not null
 node_local_id  | integer | not null
 node_offset    | integer | not null
Indexes:
    "t_node_pkey" PRIMARY KEY, btree (node_global_id)
    "xt_node_01" UNIQUE, btree (node_doc_id, node_local_id)
Foreign-key constraints:
    "t_node_node_doc_id_fkey" FOREIGN KEY (node_doc_id) REFERENCES
t_document(doc_id) MATCH FULL

                           Table "public.t_document"
       Column        |  Type   |                   Modifiers
---------------------+---------+------------------------------------------------
 doc_id              | integer | not null default
nextval('seq_document'::text)
 doc_content_id      | integer |
 doc_lr_id           | integer | not null
 doc_url             | text    |
 doc_start           | integer |
 doc_end             | integer |
 doc_is_markup_aware | boolean | not null
Indexes:
    "t_document_pkey" PRIMARY KEY, btree (doc_id)
    "xt_document_01" UNIQUE, btree (doc_lr_id)
Foreign-key constraints:
    "t_document_doc_content_id_fkey" FOREIGN KEY (doc_content_id)
REFERENCES t_doc_content(dc_id) MATCH FULL
    "t_document_doc_lr_id_fkey" FOREIGN KEY (doc_lr_id) REFERENCES
t_lang_resource(lr_id) MATCH FULL

                          Table "public.t_annotation"
      Column      |  Type   |                    Modifiers
------------------+---------+--------------------------------------------------
 ann_global_id    | integer | not null default
nextval('seq_annotation'::text)
 ann_doc_id       | integer |
 ann_local_id     | integer | not null
 ann_at_id        | integer | not null
 ann_startnode_id | integer | not null
 ann_endnode_id   | integer | not null
Indexes:
    "t_annotation_pkey" PRIMARY KEY, btree (ann_global_id)
    "xt_annotation_01" UNIQUE, btree (ann_doc_id, ann_local_id)
Foreign-key constraints:
    "t_annotation_ann_doc_id_fkey" FOREIGN KEY (ann_doc_id) REFERENCES
t_document(doc_id) MATCH FULL
    "t_annotation_ann_at_id_fkey" FOREIGN KEY (ann_at_id) REFERENCES
t_annotation_type(at_id) MATCH FULL
    "t_annotation_ann_startnode_id_fkey" FOREIGN KEY (ann_startnode_id)
REFERENCES t_node(node_global_id) MATCH FULL
    "t_annotation_ann_endnode_id_fkey" FOREIGN KEY (ann_endnode_id)
REFERENCES t_node(node_global_id) MATCH FULL

                              Table "public.t_annot_set"
  Column   |          Type          |                    Modifiers
-----------+------------------------+-------------------------------------------------
 as_id     | integer                | not null default
nextval('seq_annot_set'::text)
 as_name   | character varying(128) |
 as_doc_id | integer                | not null
Indexes:
    "t_annot_set_pkey" PRIMARY KEY, btree (as_id)
    "xt_annot_set_01" UNIQUE, btree (as_doc_id, as_name)
Foreign-key constraints:
    "t_annot_set_as_doc_id_fkey" FOREIGN KEY (as_doc_id) REFERENCES
t_document(doc_id) MATCH FULL

                        Table "public.t_as_annotation"
    Column    |  Type   |                      Modifiers
--------------+---------+-----------------------------------------------------
 asann_id     | integer | not null default nextval('seq_as_annotation'::text)
 asann_ann_id | integer | not null
 asann_as_id  | integer | not null
Indexes:
    "t_as_annotation_pkey" PRIMARY KEY, btree (asann_id)
    "xt_as_annotation_01" btree (asann_as_id)
    "xt_as_annotation_02" btree (asann_ann_id)
Foreign-key constraints:
    "t_as_annotation_asann_ann_id_fkey" FOREIGN KEY (asann_ann_id)
REFERENCES t_annotation(ann_global_id) MATCH FULL
    "t_as_annotation_asann_as_id_fkey" FOREIGN KEY (asann_as_id)
REFERENCES t_annot_set(as_id) MATCH FULL




The row count of these tables:

count: t_node  605911
count: t_document 165
count: t_annotation 618218
count: t_ annot_set 531
count: t_as_annotation  620104


thank you
renzo

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/

"Renzo Kottmann" <renzo@tzi.de> writes:
>     "t_annotation_ann_startnode_id_fkey" FOREIGN KEY (ann_startnode_id)
> REFERENCES t_node(node_global_id) MATCH FULL
>     "t_annotation_ann_endnode_id_fkey" FOREIGN KEY (ann_endnode_id)
> REFERENCES t_node(node_global_id) MATCH FULL

You need indexes on ann_startnode_id and ann_endnode_id.  There might be
some other missing indexes too --- check each of your foreign key
constraints.

Postgres doesn't force you to keep an index on the referencing side of a
foreign key ... but if you want deletes from the master table to be
fast, you'd better have one.

            regards, tom lane