Re: 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

--- On Wed, 3/30/11, Jeremy Palmer <JPalmer(at)linz(dot)govt(dot)nz> wrote:

> From: Jeremy Palmer <JPalmer(at)linz(dot)govt(dot)nz>
> Subject: [PERFORM] Slow deleting tables with foreign keys
> To: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)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(at)linz(dot)govt(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>