From: PFC <lists@peufeu.com>
To: Rodrigo Sakai <rodrigo.sakai@zanthus.com.br>
To: pgsql-performance@postgresql.org
Subject: Re: FOREIGN KEYS vs PERFORMANCE
Date: Wed, 12 Apr 2006 17:09:37 +0200
Message-ID: <op.s7whqbr7cigqcu@apollo13> (raw)
In-Reply-To: <000701c65e40$5a1ff700$4700a8c0@TREEZANTHUS>
References: <001301c65d9c$08884ef0$4700a8c0@TREEZANTHUS>
<72C39159-1A4B-4595-A978-1DF57AD42527@myrealbox.com>
<443CFE3F.5090304@logix-tt.com>
<000701c65e40$5a1ff700$4700a8c0@TREEZANTHUS>
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)!!
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: lists@peufeu.com, rodrigo.sakai@zanthus.com.br
Subject: Re: FOREIGN KEYS vs PERFORMANCE
In-Reply-To: <op.s7whqbr7cigqcu@apollo13>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox