DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=NIFTmc3FP3r/4iW0vsvlmEw0V0goIyu3uFvrw+v9GNvTccetV18dtutEdQa38ChFeVzGwP4StYa8tgGGyMC6n6bo0kq7EqJyixPjRCTCYIzUvYhTJxKrbu+n9A474+Ymq5Di5S6gkwRsxcTiYtVmpMaWrMQWNoWKvnpze/s0qog=;
Message-ID: <699909.75581.qm@web39707.mail.mud.yahoo.com>
Date: Thu, 31 Mar 2011 07:54:25 -0700 (PDT)
From: Bob Lunney <bob_lunney@yahoo.com>
Subject: Re: Slow deleting tables with foreign keys
To: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>,
	Jeremy Palmer <JPalmer@linz.govt.nz>
In-Reply-To: 
 <666FB8D75E95AE42965A0E76A5E5337E06DCBABE90@prdlsmmsg01.ad.linz.govt.nz>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

Jeremy,=0A=0ADoes table_revision have a unique index on id?  Also, I doubt =
these two indexes ever get used:=0A=0ACREATE INDEX idx_crs_action_expired_c=
reated=0A  ON table_version.bde_crs_action_revision=0A  USING btree=0A  (_r=
evision_expired, _revision_created);=0A=0ACREATE INDEX idx_crs_action_expir=
ed_key=0A  ON table_version.bde_crs_action_revision=0A  USING btree=0A  (_r=
evision_expired, audit_id);=0A=0ABob Lunney=0A=0A--- On Wed, 3/30/11, Jerem=
y Palmer <JPalmer@linz.govt.nz> wrote:=0A=0A> From: Jeremy Palmer <JPalmer@=
linz.govt.nz>=0A> Subject: [PERFORM] Slow deleting tables with foreign keys=
=0A> To: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.o=
rg>=0A> Date: Wednesday, March 30, 2011, 10:16 PM=0A> Hi All,=0A> =0A> I'm =
trying to delete one row from a table and it's taking=0A> an extremely long=
 time. This parent table is referenced by=0A> other table's foreign keys, b=
ut the particular row I'm=0A> trying to delete is not referenced any other =
rows in the=0A> associative tables. This table has the following structure:=
=0A> =0A> CREATE TABLE revision=0A> (=0A> =A0 id serial NOT NULL,=0A> =A0 r=
evision_time timestamp without time zone NOT NULL=0A> DEFAULT now(),=0A> =
=A0 start_time timestamp without time zone NOT NULL=0A> DEFAULT clock_times=
tamp(),=0A> =A0 schema_change boolean NOT NULL,=0A> =A0 "comment" text,=0A>=
 =A0 CONSTRAINT revision_pkey PRIMARY KEY (id)=0A> )=0A> WITH (=0A> =A0 OID=
S=3DFALSE=0A> );=0A> =0A> This table is referenced from foreign key by 130 =
odd other=0A> tables. The total number of rows from these referencing=0A> t=
ables goes into the hundreds of millions. Each of these=0A> tables has been=
 automatically created by script and has the=0A> same _revision_created, _r=
evision_expired fields, foreign=0A> keys and indexes. Here is an example of=
 one:=0A> =0A> CREATE TABLE table_version.bde_crs_action_revision=0A> (=0A>=
 =A0 _revision_created integer NOT NULL,=0A> =A0 _revision_expired integer,=
=0A> =A0 tin_id integer NOT NULL,=0A> =A0 id integer NOT NULL,=0A> =A0 "seq=
uence" integer NOT NULL,=0A> =A0 att_type character varying(4) NOT NULL,=0A=
> =A0 system_action character(1) NOT NULL,=0A> =A0 audit_id integer NOT NUL=
L,=0A> =A0 CONSTRAINT=0A> "pkey_table_version.bde_crs_action_revision" PRIM=
ARY KEY=0A> (_revision_created, audit_id),=0A> =A0 CONSTRAINT=0A> bde_crs_a=
ction_revision__revision_created_fkey FOREIGN KEY=0A> (_revision_created)=
=0A> =A0 =A0 =A0 REFERENCES table_version.revision (id)=0A> MATCH SIMPLE=0A=
> =A0 =A0 =A0 ON UPDATE NO ACTION ON DELETE NO=0A> ACTION,=0A> =A0 CONSTRAI=
NT=0A> bde_crs_action_revision__revision_expired_fkey FOREIGN KEY=0A> (_rev=
ision_expired)=0A> =A0 =A0 =A0 REFERENCES table_version.revision (id)=0A> M=
ATCH SIMPLE=0A> =A0 =A0 =A0 ON UPDATE NO ACTION ON DELETE NO=0A> ACTION=0A>=
 )=0A> WITH (=0A> =A0 OIDS=3DFALSE=0A> );=0A> ALTER TABLE table_version.bde=
_crs_action_revision OWNER TO=0A> bde_dba;=0A> ALTER TABLE table_version.bd=
e_crs_action_revision ALTER=0A> COLUMN audit_id SET STATISTICS 500;=0A> =0A=
> =0A> CREATE INDEX idx_crs_action_audit_id=0A> =A0 ON table_version.bde_cr=
s_action_revision=0A> =A0 USING btree=0A> =A0 (audit_id);=0A> =0A> CREATE I=
NDEX idx_crs_action_created=0A> =A0 ON table_version.bde_crs_action_revisio=
n=0A> =A0 USING btree=0A> =A0 (_revision_created);=0A> =0A> CREATE INDEX id=
x_crs_action_expired=0A> =A0 ON table_version.bde_crs_action_revision=0A> =
=A0 USING btree=0A> =A0 (_revision_expired);=0A> =0A> CREATE INDEX idx_crs_=
action_expired_created=0A> =A0 ON table_version.bde_crs_action_revision=0A>=
 =A0 USING btree=0A> =A0 (_revision_expired, _revision_created);=0A> =0A> C=
REATE INDEX idx_crs_action_expired_key=0A> =A0 ON table_version.bde_crs_act=
ion_revision=0A> =A0 USING btree=0A> =A0 (_revision_expired, audit_id);=0A>=
 =0A> =0A> All of the table have been analysed before I tried to run=0A> th=
e query.=0A> =0A> The fact the all of the foreign keys have a covering inde=
x=0A> makes me wonder why this delete is taking so long.=0A> =0A> The expla=
in for =0A> =0A> delete from table_version.revision where id =3D 1003=0A> =
=0A> =0A> Delete=A0 (cost=3D0.00..1.02 rows=3D1 width=3D6)=0A> =A0 ->=A0 Se=
q Scan on revision=A0=0A> (cost=3D0.00..1.02 rows=3D1 width=3D6)=0A> =A0 =
=A0 =A0 =A0 Filter: (id =3D 100)=0A> =0A> I'm running POstgreSQL 9.0.2 on U=
buntu 10.4=0A> =0A> Cheers=0A> Jeremy=0A> _________________________________=
_____________________________________________________________________=0A> =
=0A> This message contains information, which is confidential=0A> and may b=
e subject to legal privilege. =0A> If you are not the intended recipient, y=
ou must not peruse,=0A> use, disseminate, distribute or copy this message.=
=0A> If you have received this message in error, please notify=0A> us immed=
iately (Phone 0800 665 463 or info@linz.govt.nz)=0A> and destroy the origin=
al message.=0A> LINZ accepts no responsibility for changes to this email,=
=0A> or for any attachments, after its transmission from LINZ.=0A> =0A> Tha=
nk you.=0A> _______________________________________________________________=
_______________________________________=0A> =0A> -- =0A> Sent via pgsql-per=
formance mailing list (pgsql-performance@postgresql.org)=0A> To make change=
s to your subscription:=0A> http://www.postgresql.org/mailpref/pgsql-perfor=
mance=0A> =0A=0A=0A