public inbox for [email protected]  

help / color / mirror / Atom feed

From: Merlin Moncure <[email protected]>
To: Ariel <[email protected]>
Cc: postgres performance list <[email protected]>
Subject: Re: index of only not null, use function index?
Date: Thu, 8 Jun 2017 09:47:11 -0500
Message-ID: <CAHyXU0xm8TOJL=F-hTMZ_gMgEjRrtYCZR-t-fuNGDx+C51Gxvg@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
List-Unsubscribe:  <mailto:[email protected]?body=unsub%20pgsql-performance>

On Mon, May 22, 2017 at 10:17 AM, Ariel <[email protected]> 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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: index of only not null, use function index?
  In-Reply-To: <CAHyXU0xm8TOJL=F-hTMZ_gMgEjRrtYCZR-t-fuNGDx+C51Gxvg@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox