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@xxxxxxxxxxxx> wrote: > From: Jeremy Palmer <JPalmer@xxxxxxxxxxxx> > Subject: Slow deleting tables with foreign keys > To: "pgsql-performance@xxxxxxxxxxxxxx" <pgsql-performance@xxxxxxxxxxxxxx> > 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@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 > -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
