Postgres Pro
Downloads
Home   >   mailing lists

Thread: Does not use index on query using "field is null"?

Does not use index on query using "field is null"?

From
Bjørn T Johansen
Date:
I have a query that looks like this...:

select * from table where field is null

And when I run explain, it tells me that it uses seq scan... Is this because pg thinks that seq scan is as fast as
usingindexes or because using index on "is null" queries does 
not work?


Regards,

BTJ

--
-----------------------------------------------------------------------------------------------
Bjørn T Johansen

btj@havleik.no
-----------------------------------------------------------------------------------------------
Someone wrote:
"I understand that if you play a Windows CD backwards you hear strange Satanic messages"
To which someone replied:
"It's even worse than that; play it forwards and it installs Windows"
-----------------------------------------------------------------------------------------------

Re: Does not use index on query using "field is null"?

From
Vick Khera
Date:
2010/11/10 Bjørn T Johansen <btj@havleik.no>:
> select * from table where field is null
>
> And when I run explain, it tells me that it uses seq scan... Is this because pg thinks that seq scan is as fast as
usingindexes or because using index on "is null" queries does 
> not work?
>

Does the table have *lots* of NULLs in that column?  If the index is
not very selective, it may choose to use a seq scan since it will
result in fewer I/O ops.  This decision will be based on the relative
values of random_page_cost and seq_page_cost, and probably the
effective_cache_size as well.

Re: Does not use index on query using "field is null"?

From
Tom Lane
Date:
=?ISO-8859-1?Q?Bj=F8rn?= T Johansen <btj@havleik.no> writes:
> I have a query that looks like this...:
> select * from table where field is null

> And when I run explain, it tells me that it uses seq scan... Is this because pg thinks that seq scan is as fast as
usingindexes or because using index on "is null" queries does 
> not work?

What PG version?  Releases before 8.3 do not think that IS NULL is an
indexable condition.

            regards, tom lane

Re: Does not use index on query using "field is null"?

From
Bjørn T Johansen
Date:
On Wed, 10 Nov 2010 10:00:43 -0500
Tom Lane <tgl@sss.pgh.pa.us> wrote:

> =?ISO-8859-1?Q?Bj=F8rn?= T Johansen <btj@havleik.no> writes:
> > I have a query that looks like this...:
> > select * from table where field is null
>
> > And when I run explain, it tells me that it uses seq scan... Is this because pg thinks that seq scan is as fast as
usingindexes or because using index on "is null" queries does 
> > not work?
>
> What PG version?  Releases before 8.3 do not think that IS NULL is an
> indexable condition.
>
>             regards, tom lane
>

We are using version 8.4.x...

BTJ