Thread: 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
post the description of the t_node and t_document tables for more information
--
with regards,
S.Gnanavel
Satyam Computer Services Ltd.
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
--
with regards,
S.Gnanavel
Satyam Computer Services Ltd.
\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