Re: Slow delete when many foreign tables are defined - Mailing list pgsql-general

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