Обсуждение: constraint performance
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? If I delete any value from taba does it check tabb to see if that row is refrenced, is something marked in the taba row to indicate it is refrenced? If I update taba does it check only if I update id, or will it check no matter what field I update?
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
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?
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.