Date: Mon, 22 May 2017 11:17:31 -0400 (EDT)
From: Ariel <aspostgresql@dsgml.com>
To: pgsql-performance@postgresql.org
Subject: index of only not null, use function index?
Message-ID: <alpine.DEB.2.11.1705221035530.21869@cherryberry.dsgml.com>
User-Agent: Alpine 2.11 (DEB 23 2013-08-11)
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; format=flowed; charset=US-ASCII
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


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 (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance