Re: Slow delete when many foreign tables are defined

On Mon, 01 Dec 2014 11:00:51 -0600
Andy Colson <andy(at)squeakycode(dot)net> wrote:

> On 12/1/2014 10:21 AM, Giuseppe Sacco wrote:
> > 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.
>
> Oh, so the table structure of detail1 doesn't really match the structure
> of detail2? That'd be a pretty good reason to have lots of different
> detail tables.
>
> > If I understand it, you are suggesting to add about 25*50
> > columns in the main table and only set values for 25 columns.
>
> Nope, I didnt realize they were all different.

This is a decent place to consider using the JSON data type. Combine all
those into a single table and put all the fields that are different from
one to the next in a JSON field.

You can replicate this with other RDBMS by using a TEXT field, you just
won't be able to use PG's JSON functions if you want to be compatible
with lesser RDBMS.

Another option is a two-level deal:

CREATE TABLE main (
id SERIAL PRIMARY KEY,
);

CREATE TABLE secondary (
parent_id INT PRIMARY KEY REFERENCES main(id),
... fields common to all tables ...
);

CREATE table tertiary1 (
parent_id INT REFERENCES secondary(parent_id),
... additional fields ...
);

CREATE table tertiary2 (
parent_id INT REFERENCES secondary(parent_id),
... additional fields ...
);

This is only possible if there's only one secondary row per row in main,
so it may not work for you. But it means that a delete from main only
has to check secondary for PK references. Deletes from secondary will
be slow now, since they have to check a lot of tertiary tables, so that
might only move the problem to another table, depending on your app
design.

--
Bill Moran
I need your help to succeed:
http://gamesbybill.com