On Mon, 01 Dec 2014 11:00:51 -0600 Andy Colson <andy@xxxxxxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
