pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Bruno Wolff III <bruno@wolff.to>
To: Cestmir Hybl <cestmirl@freeside.sk>
Cc: pgsql-performance@postgresql.org
Subject: Re: Ignoring index on (A is null), (A is not null) conditions
Date: Thu, 30 Oct 2003 08:04:06 -0600
Message-ID: <20031030140406.GA7870@wolff.to> (raw)
In-Reply-To: <022001c39ed9$bfb09b20$0200a8c0@stratos>
References: <017201c39d85$533eed70$0200a8c0@stratos>
	<022001c39ed9$bfb09b20$0200a8c0@stratos>

On Thu, Oct 30, 2003 at 12:34:15 +0100,
  Cestmir Hybl <cestmirl@freeside.sk> wrote:
> Are you seeing this question as totally off-topic in this list, or there is
> really no one who knows something about indexing "is null" bits in postgres?

There was some talk about IS NULL not being able to use indexes (unless
you specifically created a partial index using that condition) a number
of months ago. You could search through the archives if you are interested
in what was said. My memory is that people thought it would be a good idea
but that it wasn't that important to get done.

> 
> > Hi,
> >
> > suppose, for simplicity, there is a table with index like this:
> >
> > create table TABLE1 (
> >   A integer
> > );
> > create index TABLE1_A on TABLE1 (A);
> >
> > My question is: why psql (7.3.3) does not use index when filtering by A IS
> > NULL, A IS NOT
> > NULL expressions?

That is a Postgres limitation. If there are only a few null values, but you
query for them a lot it may be worth creating a partial index.

> >
> > In fact, I need to filter by expression ((A is null) or (A > const)).

This is a whole different matter. Using an indexed search on > is not
necessarily a good idea. Unless you know only a small fraction of the
table (often 10% is quoted) is greater than the constant, a sequential
scan is probably a better plan than an index scan. If you know that
there is only a small fraction of the values above constant and you
know some large value greater than all values, you can try using a between
comparison to coax the planner into doing an index scan.



view thread (6+ messages)  latest in thread

Message-ID: <20031030140406.GA7870@wolff.to>
Permalink:  ../20031030140406.GA7870@wolff.to/
Also on:    postgresql.org/message-id/20031030140406.GA7870@wolff.to

 · 

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: pgsql-performance@postgresql.org
  Cc: bruno@wolff.to, cestmirl@freeside.sk
  Subject: Re: Ignoring index on (A is null), (A is not null) conditions
  In-Reply-To: <20031030140406.GA7870@wolff.to>

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

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