Slow delete by table with reference to him slef. - Mailing list pgsql-general

Hi,

CREATE TABLE mytable (
  "ids" name NOT NULL,
  "myref" name,
  CONSTRAINT "a_doc_pkey" PRIMARY KEY ("ids"),
  CONSTRAINT "$1" FOREIGN KEY ("myref") REFERENCES "mytable" ("ids") ON
DELETE NO ACTION ON UPDATE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE)



I put in the table ~ 100 000 records and after it I try to delete all
the records with:
delete from mytable;

The delete take ~ 1 h. And the processor usage is ~ 100 %.

I think the problem is in the reference to him self.

How can I improve this preformance.

For example in oracle I set all myref's to NULL and the delete is very
fast.

regards,
ivan.