Skip site navigation (1) Skip section navigation (2)

Re: ADD FOREIGN KEY (was Re: [GENERAL] 7.4Beta)

From: Andreas Pflug <pgadmin(at)pse-consulting(dot)de>
To: Greg Stark <gsstark(at)mit(dot)edu>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: ADD FOREIGN KEY (was Re: [GENERAL] 7.4Beta)
Date: 2003-09-29 22:56:32
Message-ID: 3F78B8A0.4050206@pse-consulting.de (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-hackers
Greg Stark wrote:

>  
>
>>>>So a db designer made a bloody mistake.
>>>>        
>>>>
>
>Not necessarily. If I'm never going to update or delete from the parent table
>the index would be useless. I find very few of my foreign key relationships
>actually need indexes on the child table. I usually only have the unique index
>on the parent table.
>
>And often the child table is the big table. The index would be very large and
>have awful selectivity. The last thing I want is a 5-million record table with
>half a dozen indexes each with 10-20 unique values.
>
>  
>
>>>>The problem is there's no easy way to find out what's missing.
>>>>I'd really like EXPLAIN to display all subsequent triggered queries 
>>>>also, to see the full scans caused by missing indexes.
>>>>        
>>>>
>>>I'd sure second that!
>>>      
>>>
>
>I think the root of problem here is the same as the root of the problem with
>foreign key checks being slow for large batch updates and inserts. Namely that
>foreign key constraint checks are being handled as a million small queries.
>
Apart from missing indices, this is certainly a problem. Statement level 
triggers will solve this, as soon as they are fully implemented and 
support OLD and NEW record sets.

Regards,
Andreas


In response to

pgsql-hackers by date

Next:From: Neil ConwayDate: 2003-09-29 23:00:38
Subject: Re: deprecating the use of OIDs
Previous:From: Stephan SzaboDate: 2003-09-29 22:55:04
Subject: Re: ADD FOREIGN KEY (was Re: [GENERAL] 7.4Beta)

pgsql-general by date

Next:From: Lamar OwenDate: 2003-09-29 23:26:40
Subject: Re: Where is libpq++ on redhat 9?
Previous:From: Stephan SzaboDate: 2003-09-29 22:55:04
Subject: Re: ADD FOREIGN KEY (was Re: [GENERAL] 7.4Beta)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group