8

In SQL, do all the common considerations about indexes apply if everything I'll ever want to query (in the where condition) is when a field is not null?

Is there a special configuration of the index I can apply to cover this specific case?

Eg:

select id
from Accesses
where token is not null;

Is it worth to add the index on token as much as it would be if I had to search for specific token values?

My database is Postgres.

ecoologic
  • 191
  • 1
  • 7
  • 3
    Have a look here, where Oracle decided that null is a real value after all. So yes, you do need to index it, assuming that it otherwise needs indexing (sometimes it doesn't... cardinality, etc., the usual rules apply). – Robert Harvey Jul 15 '15 at 14:33

3 Answers3

6
SELECT id FROM accesses WHERE token IS NOT NULL;

The perfect index for this specific query would be a partial index:

CREATE INDEX accesses_foo_idx ON accesses(id) WHERE token IS NOT NULL;

The index condition is the important part.
On top of it, since you only retrieve id which is covered by the index, you can get index-only scans out of this (if the table is vacuumed enough).

Effectiveness grows with the number of rows excluded from the index this way (and also with the number of columns in the table). I.e.: especially beneficial for few non-null values in token (in a table with many more columns).

Related:

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
2

You could always test this against your data and possibly use some sort of query analyzer.

Since there are a lot of factors for "common considerations about indexes," you should consider things like: data size, data type, and inserts, updates, deletes and not just selects. How often do you run the query? Is this an occasional check for blanks? How often does this table's data get modified? You can compare and contrast having an index in your system to see if it makes sense.

JeffO
  • 366
  • 1
  • 5
1

Pretty simple. Create the index and look at the query plan for an is null search. I tested in MS-SQL and the answer is yes search on is null used the index. Search on is not null also used the index.

paparazzo
  • 5,043
  • 1
  • 18
  • 32