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

From: Ariel <[email-masked]>
Date: Mon, 22 May 2017 11:17:31 -0400 (EDT)

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?

  	-Ariel

-- 
Sent via pgsql-performance mailing list ([email-masked])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

---

From: Merlin Moncure <[email-masked]>
Date: Thu, 8 Jun 2017 09:47:11 -0500

> 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

---

From: Tom Lane <[email-masked]>
Date: Thu, 08 Jun 2017 10:58:40 -0400

> of some generality.

Depends on the datatype really.  Because of alignment considerations,
the index tuples will be the same size for any column value <= 4 bytes,
or <= 8 bytes on 64-bit hardware.  So if this is an integer column,
or even bigint on 64-bit, you won't save any space with the first
index definition.  If it's a text column with an average width larger
than what I just mentioned, you could save some space that way.

In general, indexes on expressions are a tad more expensive to maintain
than indexes on plain column values.  And the second index at least has
the potential to be useful for other queries than the one you're thinking
about.  So personally I'd go with the second definition unless you can
show that there's a really meaningful space savings with the first one.

> Having said that, what I typically do in such

Right, you can frequently get a lot of mileage out of indexing something
that's unrelated to the predicate condition, but is also needed by the
query you want to optimize.

			regards, tom lane

---

From: Jeremy Finzel <[email-masked]>
Date: Thu, 8 Jun 2017 11:05:40 -0500

Normally, I find that in these situations, it makes sense to index the
primary key of the table WHERE col is not null, because it will usually
cover the largest number of cases, and is much better than a two-value
boolean index, for example.

---

From: Merlin Moncure <[email-masked]>
Date: Thu, 8 Jun 2017 14:57:38 -0500

> boolean index, for example.

[meta note: please try to avoid top-posting]

Yeah, if you index the primary key and query it like this:

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

SELECT pkey FROM table WHERE col IS NOT NULL
ORDER BY pkey LIMIT n;

This can give the best possible results since this can qualify for an
index only scan :-).

merlin