Thread: FK index q'n

Given this table:

CREATE TABLE foo (
     id integer primary key,
     bar_id integer references bar (id)
);
and provided that bar.id is itself a PK, do I still need to create an
index on bar_id if often doing queries like:
SELECT MIN(id) FROM foo WHERE bar_id IS NULL;

Table foo will contain a static number of rows (from 2,000 to 10,000 --
yet undecided) only doing SELECT & UPDATE.

Thanks.

rihad <rihad@mail.ru> writes:
> Given this table:
> CREATE TABLE foo (
>      id integer primary key,
>      bar_id integer references bar (id)
> );
> and provided that bar.id is itself a PK, do I still need to create an
> index on bar_id if often doing queries like:
> SELECT MIN(id) FROM foo WHERE bar_id IS NULL;

The FK relationship as such is only a reason to create an index if you
frequently do updates or deletes in table bar.  For such operations, the
database has to check if there are any matching rows in foo, and an
index on foo.bar_id makes that go faster.

Now as far as the above-illustrated query goes, no simple index is going
to help it, because IS NULL is not an indexable operation.  If you are
really concerned about queries of that specific form, you could make a
partial index

    create index fooi on foo (id) where bar_id is null;

            regards, tom lane

Tom Lane wrote:
> rihad <rihad@mail.ru> writes:
> > Given this table:
> > CREATE TABLE foo (
> >      id integer primary key,
> >      bar_id integer references bar (id)
> > );
> > and provided that bar.id is itself a PK, do I still need to create an
> > index on bar_id if often doing queries like:
> > SELECT MIN(id) FROM foo WHERE bar_id IS NULL;
>
> Now as far as the above-illustrated query goes, no simple index is going
> to help it, because IS NULL is not an indexable operation.

Unless you are on 8.3, that is.

--
Alvaro Herrera                 http://www.amazon.com/gp/registry/CTMLCN8V17R4
Y dijo Dios: "Que sea Satanás, para que la gente no me culpe de todo a mí."
"Y que hayan abogados, para que la gente no culpe de todo a Satanás"