Обсуждение: Slow deleting tables with foreign keys

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
othertable's foreign keys, but the particular row I'm trying to delete is not referenced any other rows in the
associativetables. 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
tablesgoes 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@linz.govt.nz) and
destroythe original message. 
LINZ accepts no responsibility for changes to this email, or for any attachments, after its transmission from LINZ.

Thank you.
______________________________________________________________________________________________________

Jeremy,

Does table_revision have a unique index on id?  Also, I doubt these two indexes ever get used:

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

Bob Lunney

--- On Wed, 3/30/11, Jeremy Palmer <JPalmer@linz.govt.nz> wrote:

> From: Jeremy Palmer <JPalmer@linz.govt.nz>
> Subject: [PERFORM] Slow deleting tables with foreign keys
> To: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
> Date: Wednesday, March 30, 2011, 10:16 PM
> 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@linz.govt.nz)
> 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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

Hi Bob,

The "table_version.revision" ("revision" is the same) table has a primary key on id because of the PK "revision_pkey".
Actuallyat the moment there are only two rows in the table table_version.revision! 

Thanks for the tips about the indexes. I'm still in the development and tuning process, so I will do some analysis of
theindex stats to see if they are indeed redundant. 

Cheers,
Jeremy
________________________________________
From: Bob Lunney [bob_lunney@yahoo.com]
Sent: Friday, 1 April 2011 3:54 a.m.
To: pgsql-performance@postgresql.org; Jeremy Palmer
Subject: Re: [PERFORM] Slow deleting tables with foreign keys

Jeremy,

Does table_revision have a unique index on id?  Also, I doubt these two indexes ever get used:

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

Bob Lunney
______________________________________________________________________________________________________

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@linz.govt.nz) and
destroythe original message. 
LINZ accepts no responsibility for changes to this email, or for any attachments, after its transmission from LINZ.

Thank you.
______________________________________________________________________________________________________

On Wed, Mar 30, 2011 at 10:16 PM, Jeremy Palmer <JPalmer@linz.govt.nz> wrote:
> 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
othertable's foreign keys, but the particular row I'm trying to delete is not referenced any other rows in the
associativetables. 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
tablesgoes 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

EXPLAIN ANALYZE can be useful in these kinds of situations, as it will
tell you where the time is going.  e.g.:

rhaas=# explain analyze delete from foo where a = 2;
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Delete on foo  (cost=0.00..8.27 rows=1 width=6) (actual
time=0.054..0.054 rows=0 loops=1)
   ->  Index Scan using foo_a_key on foo  (cost=0.00..8.27 rows=1
width=6) (actual time=0.028..0.032 rows=1 loops=1)
         Index Cond: (a = 2)
 Trigger for constraint bar_a_fkey: time=5.530 calls=1
 Total runtime: 5.648 ms
(5 rows)

In your case you probably will have lots of "Trigger for constraint
blahblah" lines and you can see which one or ones are taking all the
time, which might give you a clue where to go with it.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company