Will PG use composite index to enforce foreign keys?

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