Re: Slow delete when many foreign tables are defined

Il giorno lun, 01/12/2014 alle 09.49 -0600, Andy Colson ha scritto:
> 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.
[...]
> 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).

Basically we do have a lot of different attributes on each details
tables. Let's say we use 20-30 specific columns in each of them, so why
should we waste disk space and CPU cycles for handling all these columns
in one table? If I understand it, you are suggesting to add about 25*50
columns in the main table and only set values for 25 columns.

Moreover, our ORM would probably get crazy :-)

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

This would probably help, but we are blocked on ANSI SQL for easily
porting our application to other DBMSes.

Bye,
Giuseppe