pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Cestmir Hybl <cestmirl@freeside.sk>
To: pgsql-performance@postgresql.org
Subject: Re: Ignoring index on (A is null), (A is not null) conditions
Date: Thu, 30 Oct 2003 12:34:15 +0100
Message-ID: <022001c39ed9$bfb09b20$0200a8c0@stratos> (raw)
References: <017201c39d85$533eed70$0200a8c0@stratos>

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?

Regards
CH


> 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?
>
> In fact, I need to filter by expression ((A is null) or (A > const)).
>
> Is there a way to filter by this expression using index?
>
> Functional index cannot be used (except strange solution with CASE-ing and
> converting NULL values into some integer constant)
>
>
>
> --------------------------------------------------------------------------
--
> --
>  Index Scan using table1_a on table1  (cost=0.00..437.14 rows=29164
width=4)
>    Index Cond: (a > 1000)
> --------------------------------------------------------------------------
--
> --
>  Seq Scan on table1  (cost=0.00..448.22 rows=1 width=4)
>    Filter: (a IS NULL)
> --------------------------------------------------------
>  Seq Scan on table1  (cost=0.00..448.22 rows=30222 width=4)
>    Filter: (a IS NOT NULL)
> ------------------------------------------------------------
>  Seq Scan on table1  (cost=0.00..523.77 rows=29164 width=4)
>    Filter: ((a IS NULL) OR (a > 1000))
> ------------------------------------------------------------
>
>
> CH




view thread (6+ messages)  latest in thread

Message-ID: <022001c39ed9$bfb09b20$0200a8c0@stratos>
Permalink:  ../022001c39ed9$bfb09b20$0200a8c0@stratos/
Also on:    postgresql.org/message-id/022001c39ed9$bfb09b20$0200a8c0@stratos

 · 

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: cestmirl@freeside.sk
  Subject: Re: Ignoring index on (A is null), (A is not null) conditions
  In-Reply-To: <022001c39ed9$bfb09b20$0200a8c0@stratos>

* 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