Using index for IS NULL query

From: "Andrus" <kobruleht2(at)hot(dot)ee>
To: pgsql-performance(at)postgresql(dot)org
Subject: Using index for IS NULL query
Date: 2008-11-11 19:55:55
Message-ID: gfcprb$m40$1@news.hub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Index is not used for

is null

condition:

create index makse_dokumnr_idx on makse(dokumnr);
explain select
sum( summa)
from MAKSE
where dokumnr is null

"Aggregate (cost=131927.95..131927.96 rows=1 width=10)"
" -> Seq Scan on makse (cost=0.00..131927.94 rows=1 width=10)"
" Filter: (dokumnr IS NULL)"

Table makse contains 1200000 rows and about 800 rows with dokumnr is null so
using index is much faster that seq scan.
How to fix ?

Andrus.

"PostgreSQL 8.1.4 on i686-pc-linux-gnu, compiled by GCC
i686-pc-linux-gnu-gcc (GCC) 3.4.6 (Gentoo 3.4.6-r1, ssp-3.4.5-1.0,
pie-8.7.9)"

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2008-11-11 20:45:15 Re: Using index for IS NULL query
Previous Message Tom Lane 2008-11-11 19:15:46 Re: Oddity with view (now with test case)