PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Efficiently indexing sparse nullable column in Postgres

I need to index a Postgres column that consists of mostly NULL values. I don’t want the NULL values to be stored in the index (to make the index smaller, and to speed up row insertion). However, adding a partial index on IS NOT NULL only lets me search efficiently for all non-null values, not for a specific non-null value.

How can I define an index on the value in this column that excludes only NULL values from the index?

Tags:

2

Answers


  1. 0

    If most of the values are NULL, the most efficient way to search for NULL is a sequential scan of the table. So an index is not going to help you for such a search. Consequently, you might as well go with the partial index; you won’t lose anything.

    Since PostgreSQL v13, B-tree indexes "deduplicate" repeated values, and columns with lots of NULL values can benefit from that. So even if you don’t create a partial index, the index will be fairly small (but you will pay the price for index modifications whenever you insert a NULL).

    Login or Signup to reply.
  2. 0

    partial index on IS NOT NULL only lets me search efficiently for all non-null values, not for a specific non-null value.

    That sounds like one of these:

    create index on tbl(col is not null);
create index on tbl(col is not null, col);
create index on tbl(col, col is not null);

    But none of the above is a partial index. That would be

    create index on tbl(col)where(col is not null);

    And it does let you "search efficiently for specific non-null values" while being exactly "an index on the value in this column that excludes only NULL values from the index".

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search