Date: Wed, 12 Apr 2006 17:09:37 +0200
To: "Rodrigo Sakai" <rodrigo.sakai@zanthus.com.br>,
	pgsql-performance@postgresql.org
Subject: Re: FOREIGN KEYS  vs  PERFORMANCE
References: <001301c65d9c$08884ef0$4700a8c0@TREEZANTHUS>
	<72C39159-1A4B-4595-A978-1DF57AD42527@myrealbox.com>
	<443CFE3F.5090304@logix-tt.com>
	<000701c65e40$5a1ff700$4700a8c0@TREEZANTHUS>
From: PFC <lists@peufeu.com>
Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Message-ID: <op.s7whqbr7cigqcu@apollo13>
In-Reply-To: <000701c65e40$5a1ff700$4700a8c0@TREEZANTHUS>
User-Agent: Opera M2/8.51 (Linux, build 1462)


	What kinds of operations are made slow by foreign key checks ? Is it :
	- Simple checks on INSERT ?
	- Simple checks on UPDATE ?
	- Cascaded deletes ?
	- Cascaded updates ?
	- Locks ?
	- Something else ?

	Foreign keys are to ensure that the value in a column is always part of a  
specific set (the referenced table). If this set changes very rarely, like  
the list of countries in the world, or the list of states in a country, or  
the various possible states an order can be in (received, processed,  
shipped...) then it's alright to skip the check if you're sure your  
application will insert a valid value. For some other situations, doing  
the check in the application will need some queries and could be slower  
(and certainly will be more complicated...)

	Are you sure you're not missing a few indexes, which would then force  
fkey checks to use sequential scans ?

>   Thanks for all help!! But my problem is with performance, I agree with  
> all
> of you, the RI must be maintained by the database, because a bunch of
> reasons that everyone knows!
>   But, I'm dealing with a very huge database that servers more than 200
> clientes at the same time, and because of it, each manipulation (delete,
> insert, update, select) on the database have a poor performance. So, if  
> we
> deal with RI in each client station, we take this work off the database!
>   The application is an ERP developed with DELPHI + (postgresql or  
> oracle or
> sql server)!!