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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
