Re: constraint performance

On Tuesday 08 Apr 2003 9:49 pm, Joseph Shraibman wrote:
> create table taba( id int PRIMARY KEY, name text);
> create table tabb( rid int PRIMARY KEY REFERENCES taba(id));
>
> insert into taba values (1,'1');
> insert into taba values (2,'2');
>
> insert into tabb values(1);
>
> explain delete from taba where id = 1;
> explain delete from taba where id = 2;

> The explain doesn't show any checking of the constraint, but the checking
> is done somewhere.  How much does the checking cost?

Basically the foreign-key constraint sets up triggers to do the checking. The
cost will depend on how many rows are involved. If you had 1 million rows in
tabb and deleted those with id>99999 that would require checking a lot of
deletions.

>  If I delete any value
> from taba does it check tabb to see if that row is refrenced

You can set it up to forbid deletions from taba while another row references
it or to cascade the delete (i.e. delete all referencing rows in tabb). You
might want to cascade if you had two tables: invoice_header and invoice_line
where invoice_line references invoice_header. Deleting an invoice_header
should then delete the invoice_line rows.

> is something
> marked in the taba row to indicate it is refrenced?

A trigger is placed on the table. Nothing is marked on rows AFAIK, that is
there isn't some flag on id=2 and none on id=3.

>  If I update taba does
> it check only if I update id, or will it check no matter what field I
> update?

At present it always checks, I'm afraid. Worse still it locks the rows in
question to make sure another process doesn't delete the referenced row while
your transaction is in progress.

Jan Wieck has done some work on this recently though: see "FK deadlock problem
addressed" on this list or pgsql-hackers. I believe he's produced a patch for
7.3 and is looking for testers so if you can compile from source that might
interest you.

PS - very good posting - subject was to the point, example was short and clear
and you packed in half a dozen clear individual questions.
--
  Richard Huxton