From: Bob Lunney <bob_lunney@yahoo.com>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
To: Jeremy Palmer <JPalmer@linz.govt.nz>
Subject: Re: Slow deleting tables with foreign keys
Date: Thu, 31 Mar 2011 07:54:25 -0700 (PDT)
Message-ID: <699909.75581.qm@web39707.mail.mud.yahoo.com> (raw)
In-Reply-To: <666FB8D75E95AE42965A0E76A5E5337E06DCBABE90@prdlsmmsg01.ad.linz.govt.nz>
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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: bob_lunney@yahoo.com, JPalmer@linz.govt.nz
Subject: Re: Slow deleting tables with foreign keys
In-Reply-To: <699909.75581.qm@web39707.mail.mud.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox