NULLs taking up space in foreign key indexes

[Ole Laursen]

Hi!

I recently noticed that the default indexes that Django generates for foreign keys also index NULL values, at least for PostgreSQL. Is this on purpose?

From my digging, it looks like PostgreSQL used to exclude NULL values from the index, but not since some years. It's relatively easy to skip them by appending "where ... is not null" to the index creation, like this:

  create index for_bar_id_index on foo (bar_id) where bar_id is not null;

This comes up because nullable foreign keys are for optional relations, and I sometimes have big tables with a bunch of mostly NULL foreign keys (NULLs are cheap in PostgreSQL), and the indexes can then dwarf the table.

I have always assumed that the default indexes where for reverse joining and deleting purposes only so keeping track of NULLs doesn't actually seem necessary.

Ole