Slow delete when many foreign tables are defined

From: Giuseppe Sacco <giuseppe(at)eppesuigoccas(dot)homedns(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: Slow delete when many foreign tables are defined
Date: 2014-12-01 15:23:28
Message-ID: 1417447408.19194.8.camel@eppesuigoccas.homedns.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,
I have a main table and a lot of "details" tables that reference the
main one.

Every time I delete a record from the main table, a check is done on
every details table that contain a foreign key toward main table.

This is a simplified schema:

create table main (
type varchar,
serial numeric,
description varchar not null,
constraint "mainpk" primary key (type,serial));

create table details1 (
type varchar check (type = '1'),
serial numeric,
details1 varchar not null,
constraint "details1pk" primary key (type,serial),
constraint "details1fk" foreign key (type,serial) references
main(type,serial));

create table details2 (
type varchar check (type = '2'),
serial numeric,
details2 varchar not null,
constraint "details2pk" primary key (type,serial),
constraint "details2fk" foreign key (type,serial) references
main(type,serial));

and suppose I have about 50-100 of these details tables, and about a
thousand records per each detail table. All detail tables use different
value for column "type".

Now, when I delete a record, I should delete it from a detail table and
from main table.

When I delete from main table, postgresql check for reference from all
details tables, while I would only check from the details table that
have the column "type" corrected.

insert into main values ('1',1,'desc');
insert into main values ('2',1,'desc');
insert into details1 values ('1',1,'desc');
insert into details2 values ('2',1,'desc');

begin;
delete from details2;
explain analyze delete from main where type = '2';

QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Delete on main (cost=4.17..11.28 rows=3 width=6) (actual time=0.015..0.015 rows=0 loops=1)
-> Bitmap Heap Scan on main (cost=4.17..11.28 rows=3 width=6) (actual time=0.011..0.011 rows=1 loops=1)
Recheck Cond: ((type)::text = '2'::text)
Heap Blocks: exact=1
-> Bitmap Index Scan on mainpk (cost=0.00..4.17 rows=3 width=0) (actual time=0.007..0.007 rows=1 loops=1)
Index Cond: ((type)::text = '2'::text)
Planning time: 0.035 ms
Trigger for constraint details1fk: time=0.107 calls=1
Trigger for constraint details2fk: time=0.197 calls=1
Execution time: 0.331 ms

As you may see, the delete operation call trigger details1fk even if
data in table details1 cannot be impacted by this delete.

You may think what happen with about 50 details tables...

Is there any way to make it work faster?

Thank you very much,
Giuseppe

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2014-12-01 15:34:38 Re: PG94RC1- plv8 functions - problem with input parameter length
Previous Message Misa Simic 2014-12-01 15:20:54 Re: PG94RC1- plv8 functions - problem with input parameter length