Обсуждение: Will PG use composite index to enforce foreign keys?
Hi - I know that the foreign key machinery will use an index on the referring column if one exists. My question is whether it will use a composite index? For instance: create table allLemmaSenseMap ( wordID integer references allLemmas, senseID integer references allSenses, primary key (wordID, senseID) ); If I delete something from allLemmas, will the FK check use the PK index above? (I know I should at least have an index on senseID as well, because of the other foreign key.) As a secondary question, is there any way I could have answered this myself, using analyze, the system catalogs, etc? ANALYZE DELETE doesn't seem to show the FK checking that must go on behind the scenes. Thanks. - John D. Burger MITRE
On Nov 29, 2007 10:51 AM, John Burger <john@mitre.org> wrote: > Hi - > > I know that the foreign key machinery will use an index on the > referring column if one exists. My question is whether it will use a > composite index? For instance: > > create table allLemmaSenseMap ( > wordID integer references allLemmas, > senseID integer references allSenses, > primary key (wordID, senseID) > ); > > If I delete something from allLemmas, will the FK check use the PK > index above? (I know I should at least have an index on senseID as > well, because of the other foreign key.) Yes. It will > As a secondary question, is there any way I could have answered this > myself, using analyze, the system catalogs, etc? ANALYZE DELETE > doesn't seem to show the FK checking that must go on behind the scenes. You could have coded up an example to see if it worked I guess. Here's a short example: create table a (i int, j int, info text, primary key (i,j)); create table b (o int, p int, moreinfo text, foreign key (o,p) references a); insert into a values (1,2,'abc'); insert into b values (1,2,'def'); INSERT 0 1 insert into b values (1,3,'def'); ERROR: insert or update on table "b" violates foreign key constraint "b_o_fkey" DETAIL: Key (o,p)=(1,3) is not present in table "a". delete from a; ERROR: update or delete on table "a" violates foreign key constraint "b_o_fkey" on table "b" DETAIL: Key (i,j)=(1,2) is still referenced from table "b".
Scott Marlowe wrote: >> As a secondary question, is there any way I could have answered this >> myself, using analyze, the system catalogs, etc? ANALYZE DELETE >> doesn't seem to show the FK checking that must go on behind the >> scenes. > > You could have coded up an example to see if it worked I guess. > Here's a short example: > > create table a (i int, j int, info text, primary key (i,j)); > create table b (o int, p int, moreinfo text, foreign key (o,p) > references a); > insert into a values (1,2,'abc'); > insert into b values (1,2,'def'); > INSERT 0 1 > insert into b values (1,3,'def'); > ERROR: insert or update on table "b" violates foreign key > constraint "b_o_fkey" > DETAIL: Key (o,p)=(1,3) is not present in table "a". > delete from a; > ERROR: update or delete on table "a" violates foreign key constraint > "b_o_fkey" on table "b" > DETAIL: Key (i,j)=(1,2) is still referenced from table "b". But this doesn't really match my question - I wanted to know whether checking an FK on =one= column would use a composite key on =several= columns. Modifying your example: > create table a (i int PRIMARY KEY, j int, info text); > create table b (o int REFERENCES A, p int, moreinfo text, PRIMARY > KEY (O,P)); > insert into a values (1,2,'abc'); > insert into b values (1,2,'def'); > > delete from a where i = 1; Here, the FK is a simple one, and the referential integrity machinery simply needs to check whether there is a row in table B with O=1. My question is whether it will use the composite PK index. I guess a generalization of my question is whether the FK-checking machinery simply does a SELECT against the referencing column. That is, in this example, if the following effectively happens: SELECT * FROM B WHERE O = 1; then PG will use whatever index might make the query faster. Is this in fact the case, that I should think of the FK machinery as simply doing the appropriate SELECT? Thanks. - John D. Burger MITRE
"John Burger" <john@mitre.org> writes: > Scott Marlowe wrote: > >>> As a secondary question, is there any way I could have answered this >>> myself, using analyze, the system catalogs, etc? ANALYZE DELETE >>> doesn't seem to show the FK checking that must go on behind the scenes. >> >> You could have coded up an example to see if it worked I guess. >> Here's a short example: >> >> create table a (i int, j int, info text, primary key (i,j)); >> create table b (o int, p int, moreinfo text, foreign key (o,p) references >> a); >> insert into a values (1,2,'abc'); >> insert into b values (1,2,'def'); >> INSERT 0 1 >> insert into b values (1,3,'def'); >> ERROR: insert or update on table "b" violates foreign key constraint >> "b_o_fkey" >> DETAIL: Key (o,p)=(1,3) is not present in table "a". >> delete from a; >> ERROR: update or delete on table "a" violates foreign key constraint >> "b_o_fkey" on table "b" >> DETAIL: Key (i,j)=(1,2) is still referenced from table "b". > > But this doesn't really match my question - I wanted to know whether checking > an FK on =one= column would use a composite key on =several= columns. > Modifying your example: > >> create table a (i int PRIMARY KEY, j int, info text); >> create table b (o int REFERENCES A, p int, moreinfo text, PRIMARY KEY >> (O,P)); >> insert into a values (1,2,'abc'); >> insert into b values (1,2,'def'); >> >> delete from a where i = 1; > > > Here, the FK is a simple one, and the referential integrity machinery simply > needs to check whether there is a row in table B with O=1. My question is > whether it will use the composite PK index. > > I guess a generalization of my question is whether the FK-checking machinery > simply does a SELECT against the referencing column. It does > That is, in this > example, if the following effectively happens: > > SELECT * FROM B WHERE O = 1; Actually the query is (effectively, assuming your equality operators are named "=" and the columns match in type) SELECT 1 FROM ONLY B x WHERE col1=? AND col2=? ... FOR SHARE OF x Since it has to take a lock on the record found to ensure it doesn't disappear before your transaction finishes. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support!
Gregory Stark wrote: >> I guess a generalization of my question is whether the FK- >> checking machinery >> simply does a SELECT against the referencing column. > > It does > Actually the query is (effectively, assuming your equality > operators are named > "=" and the columns match in type) > > SELECT 1 > FROM ONLY B x > WHERE col1=? > AND col2=? > ... > FOR SHARE OF x > > Since it has to take a lock on the record found to ensure it > doesn't disappear > before your transaction finishes. Awesome, this tells me a lot! So I can use EXPLAIN ANALYZE and a query like this to reason about what indexes I might need on my foreign key references. Thanks! - John D. Burger MITRE