Re: constraint performance

On Wed, 9 Apr 2003, Joseph Shraibman wrote:

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

It shouldn't. I think he mistyped tabb when he meant taba.

> >> 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)?

The penalty depends on the parameters of the constraint, for the case
above it's a select on tabb per row changed, so you'll almost always want
the referencing columns to be indexed.

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

This is not entirely right.

When taba is updated, the check on taba is still done (because it's in the
wrong place) but the check on tabb isn't AFAICS. Neither needs to be
done AFAICT, and I think it's just that I stuck the pk check in the wrong
spot and it should get moved and I'm going to do that and try it.