Re: [GENERAL] FK index q'n

From: Tom Lane <tgl(at)sss.pgh.pa.us>
Date: Fri Nov 30 2007 - 10:48:07 EST

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

---------------------------(end of broadcast)---------------------------

TIP 2: Don't 'kill -9' the postmaster Received on Fri Nov 30 10:49:07 2007