Re: constraint performance

Richard Huxton wrote:
> 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.
>
tabb refrences taba. When I delete from taba it should check tabb, but why should it
check taba when I delete from tabb?
>
>> 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

That wasn't my question. I'm concerned about hidden performace drains that aren't showing
up in explain. What I want to know is what is the performance penalty for
updating/deleting rows in taba. Should I index tabb to speed up the trigger (in this
example it already is)?

>
>> 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.

Ugh. Is that particular issue on the TODO list?