Re: Slow deleting tables with foreign keys

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