On Mon, May 22, 2017 at 10:17 AM, Ariel <aspostgresql@xxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
