Strange delete behaviour

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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

--
with regards,
S.Gnanavel
Satyam Computer Services Ltd.

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);

Post the result of
\d t_node t_document

On 8/1/05, Renzo Kottmann <renzo@tzi.de> wrote:

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);

--
with regards,
S.Gnanavel
Satyam Computer Services Ltd.

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