Re: Slow delete when many foreign tables are defined

From: Andy Colson <andy(at)squeakycode(dot)net>
To: Giuseppe Sacco <giuseppe(at)eppesuigoccas(dot)homedns(dot)org>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Slow delete when many foreign tables are defined
Date: 2014-12-01 17:00:51
Message-ID: 547C9EC3.5010705@squeakycode.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.
>
> Bye,
> Giuseppe
>
>
>

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.

Hum.. how different though? Maybe you could combine the similar one?
Maybe cutting the # of detail tables down from 50-100 to 25 would
increase speed enough?

-Andy

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Andy Colson 2014-12-01 17:05:37 Re: Slow delete when many foreign tables are defined
Previous Message Nelson Green 2014-12-01 16:42:00 Programmatic access to interval units