Обсуждение: Will PG use composite index to enforce foreign keys?

Поиск
Список
Период
Сортировка

Will PG use composite index to enforce foreign keys?

От
John Burger
Дата:
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



Re: Will PG use composite index to enforce foreign keys?

От
"Scott Marlowe"
Дата:
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".

Re: Will PG use composite index to enforce foreign keys?

От
John Burger
Дата:
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



Re: Will PG use composite index to enforce foreign keys?

От
Gregory Stark
Дата:
"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!

Re: Will PG use composite index to enforce foreign keys?

От
John Burger
Дата:
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