ON DELETE CACADE is very slow

Question

I am using Postgres 8.4. My system configuration is window 7 32 bit 4 gb ram and 2.5ghz.

I have a database in Postgres with 10 tables t1, t2, t3, t4, t5.....t10.

t1 has a primary key a sequence id which is a foreign key reference to all other tables.

The data is inserted in database (i.e. in all tables) apart from t1 all other tables have nearly 50,000 rows of data but t1 has one 1 row whose primary key is referenced from all other tables. Then I insert the 2nd row of data in t1 and again 50,000 rows with this new reference in other tables.

The issue is when I want to delete all the data entries that are present in other tables:

delete from t1 where column1='1'

This query takes nearly 10 min to execute.

I created indexes also and tried but the performance is not at all improving. what can be done?

I have mentioned a sample schema below

CREATE TABLE t1
(
  c1 numeric(9,0) NOT NULL,
  c2 character varying(256) NOT NULL,
  c3ver numeric(4,0) NOT NULL,
  dmlastupdatedate timestamp with time zone NOT NULL,
  CONSTRAINT t1_pkey PRIMARY KEY (c1),
  CONSTRAINT t1_c1_c2_key UNIQUE (c2)
);

CREATE TABLE t2
(
  c1 character varying(100),
  c2 character varying(100),
  c3 numeric(9,0) NOT NULL,
  c4 numeric(9,0) NOT NULL,
  tver numeric(4,0) NOT NULL,
  dmlastupdatedate timestamp with time zone NOT NULL,
  CONSTRAINT t2_pkey PRIMARY KEY (c3),
  CONSTRAINT t2_fk FOREIGN KEY (c4)
      REFERENCES t1 (c1) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE,
  CONSTRAINT t2_c3_c4_key UNIQUE (c3, c4)
);

CREATE INDEX t2_index ON t2 USING btree (c4);

Let me know if there is anything wrong with the schema.

Answer 1

With bigger tables and more than just two or three values, you need an index on the referenced column (t1.c1) as well as the referencing columns (t2.c4, ...).

But if your description is accurate, that can not be the cause of the performance problem in your scenario. Since you have only 2 distinct values in t1, there is just no use for an index. A sequential scan will be faster.

Anyway, I re-enacted what you describe in Postgres 9.1.9

CREATE TABLE t1
( c1 numeric(9,0) PRIMARY KEY,
  c2 character varying(256) NOT NULL,
  c3ver numeric(4,0) NOT NULL,
  dmlastupdatedate timestamptz NOT NULL,
  CONSTRAINT t1_uni_key UNIQUE (c2)
);

CREATE temp TABLE t2
( c1 character varying(100),
  c2 character varying(100),
  c3 numeric(9,0) PRIMARY KEY,
  c4 numeric(9,0) NOT NULL,
  tver numeric(4,0) NOT NULL,
  dmlastupdatedate timestamptz NOT NULL,
  CONSTRAINT t2_uni_key UNIQUE (c3, c4),
  CONSTRAINT t2_c4_fk FOREIGN KEY (c4)
      REFERENCES t1(c1) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
);

INSERT INTO t1 VALUES 
 (1,'OZGPIGp7tgp97tßp97tß97tgP?)/GP)7gf', 234, now())
,(2,'agdsOZGPIGp7tgp97tßp97tß97tgP?)/GP)7gf', 4564, now());

INSERT INTO t2
SELECT'shOahaZGPIGp7tgp97tßp97tß97tgP?)/GP)7gf'
     ,'shOahaZGPIGp7tgp97tßp97tß97tgP?)/GP)7gf'
     , g, 2, 456, now()
from generate_series (1,50000) g

INSERT INTO t2
SELECT'shOahaZGPIGp7tgp97tßp97tß97tgP?)/GP)7gf'
     ,'shOahaZGPIGp7tgp97tßp97tß97tgP?)/GP)7gf'
     , g, 2, 789, now()
from generate_series (50001, 100000) g

ANALYZE t1;
ANALYZE t2;

EXPLAIN ANALYZE DELETE FROM t1 WHERE c1 = 1;

Total runtime: 53.745 ms

DELETE FROM t1 WHERE c1 = 1;

58 ms execution time.

Ergo, there is nothing fundamentally wrong with your schema layout.

Minor enhancements:

• You have a couple of columns defined numeric(9,0) or numeric(4,0). Unless you have a good reason to do that, you are probably a lot better off using just integer. They are smaller and faster overall. You can always add a check constraint if you really need to enforce a maximum.

• I also would use text instead of varchar(n)

• And reorder columns (at table creation time). As a rule of thumb, place fixed length NOT NULL columns first. Put timestamp and integer first and numeric or text last. More here..