Do I want an index if all I care to query is field not null

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.

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:

• Unexpected Seq Scan when doing query against boolean with value NULL

• Index optimization with dates

StackExchange Database Administrators Q#107068, answer score: 6