Re: FK index q'n

rihad <rihad(at)mail(dot)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