Re: Slow delete when many foreign tables are defined

On 12/1/2014 9:23 AM, Giuseppe Sacco wrote:
> 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
>
>
>

I can think of two options:

1) Don't use 50 different detail tables. A single detail table with the
type column will work much faster. Is there a good reason to break them
out? (# rows is not a good reason, btw).

2) Try inheritance. I have no idea if it'll help, but I thought I'd
read someplace where the planner knew a little more about what types of
rows go into which tables.

-Andy