index of only not null, use function index?

From: Ariel <aspostgresql(at)dsgml(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: index of only not null, use function index?
Date: 2017-05-22 15:17:31
Message-ID: alpine.DEB.2.11.1705221035530.21869@cherryberry.dsgml.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance


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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Merlin Moncure 2017-05-22 15:37:52 Re: Bulk persistence strategy
Previous Message Tom Lane 2017-05-22 11:50:15 Re: Bulk persistence strategy