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 i...@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
