Slow deleting tables with foreign keys

[Jeremy Palmer <JPalmer@xxxxxxxxxxxx>]

Hi All,

I'm trying to delete one row from a table and it's taking an extremely long time. This parent table is referenced by other table's foreign keys, but the particular row I'm trying to delete is not referenced any other rows in the associative tables. This table has the following structure:

CREATE TABLE revision
(
  id serial NOT NULL,
  revision_time timestamp without time zone NOT NULL DEFAULT now(),
  start_time timestamp without time zone NOT NULL DEFAULT clock_timestamp(),
  schema_change boolean NOT NULL,
  "comment" text,
  CONSTRAINT revision_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);

This table is referenced from foreign key by 130 odd other tables. The total number of rows from these referencing tables goes into the hundreds of millions. Each of these tables has been automatically created by script and has the same _revision_created, _revision_expired fields, foreign keys and indexes. Here is an example of one:

CREATE TABLE table_version.bde_crs_action_revision
(
  _revision_created integer NOT NULL,
  _revision_expired integer,
  tin_id integer NOT NULL,
  id integer NOT NULL,
  "sequence" integer NOT NULL,
  att_type character varying(4) NOT NULL,
  system_action character(1) NOT NULL,
  audit_id integer NOT NULL,
  CONSTRAINT "pkey_table_version.bde_crs_action_revision" PRIMARY KEY (_revision_created, audit_id),
  CONSTRAINT bde_crs_action_revision__revision_created_fkey FOREIGN KEY (_revision_created)
      REFERENCES table_version.revision (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT bde_crs_action_revision__revision_expired_fkey FOREIGN KEY (_revision_expired)
      REFERENCES table_version.revision (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE table_version.bde_crs_action_revision OWNER TO bde_dba;
ALTER TABLE table_version.bde_crs_action_revision ALTER COLUMN audit_id SET STATISTICS 500;


CREATE INDEX idx_crs_action_audit_id
  ON table_version.bde_crs_action_revision
  USING btree
  (audit_id);

CREATE INDEX idx_crs_action_created
  ON table_version.bde_crs_action_revision
  USING btree
  (_revision_created);

CREATE INDEX idx_crs_action_expired
  ON table_version.bde_crs_action_revision
  USING btree
  (_revision_expired);

CREATE INDEX idx_crs_action_expired_created
  ON table_version.bde_crs_action_revision
  USING btree
  (_revision_expired, _revision_created);

CREATE INDEX idx_crs_action_expired_key
  ON table_version.bde_crs_action_revision
  USING btree
  (_revision_expired, audit_id);


All of the table have been analysed before I tried to run the query.

The fact the all of the foreign keys have a covering index makes me wonder why this delete is taking so long.

The explain for 

delete from table_version.revision where id = 1003


Delete  (cost=0.00..1.02 rows=1 width=6)
  ->  Seq Scan on revision  (cost=0.00..1.02 rows=1 width=6)
        Filter: (id = 100)

I'm running POstgreSQL 9.0.2 on Ubuntu 10.4

Cheers
Jeremy
______________________________________________________________________________________________________

This message contains information, which is confidential and may be subject to legal privilege. 
If you are not the intended recipient, you must not peruse, use, disseminate, distribute or copy this message.
If you have received this message in error, please notify us immediately (Phone 0800 665 463 or info@xxxxxxxxxxxx) and destroy the original message.
LINZ accepts no responsibility for changes to this email, or for any attachments, after its transmission from LINZ.

Thank you.
______________________________________________________________________________________________________

-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance