Re: index of only not null, use function index?

On Mon, May 22, 2017 at 10:17 AM, Ariel <aspostgresql(at)dsgml(dot)com> wrote:
>
> I need to be able to quickly find rows where a column is not null (only a
> small percent of the rows will have that column not null).
>
> Should I do:
>
> CREATE INDEX ON table ((col IS NOT NULL)) WHERE col IS NOT NULL
>
> or:
>
> CREATE INDEX ON table (col) WHERE col IS NOT NULL
>
> I'm thinking the first index will make a smaller, simpler, index since I
> don't actually need to index the value of the column. But are there any
> drawbacks I may not be aware of? Or perhaps there are no actual benefits?

You are correct. I don't see any downside to converting to bool; this
will be more efficient especially if 'col' is large at the small cost
of some generality. Having said that, what I typically do in such
cases (this comes a lot in database driven work queues) something like
this:

CREATE INDEX ON table (OrderCol) WHERE col IS NOT NULL;

Where "OrderCol" is some field that defines some kind of order to the
items that you are marking off. This will give very good performance
of queries in the form of:

SELECT Col FROM table WHERE col IS NOT NULL ORDER BY OrderCol LIMIT 1;

merlin