Skip site navigation (1) Skip section navigation (2)

Re: Bug? 8.0 does not use partial index

From: Greg Stark <gsstark(at)mit(dot)edu>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Bug? 8.0 does not use partial index
Date: 2005-01-14 04:50:01
Message-ID: 87hdlkwr5y.fsf@stark.xeocode.com (view raw or flat)
Thread:
Lists: pgsql-hackers
John Hansen <john(at)geeknet(dot)com(dot)au> writes:

> > Thanks, but the behaviour seems identical. :(
> 
> odd tho, that I was never able to get null values indexed (index was
> never used) unless I used this approach....

You're mixing up the indexed column with the where clause of a partial index.
They behave differently. 

Null values *are* normally indexed in Postgres. The problem is that the
optimizer doesn't recognize IS NULL as an indexable operation, so they don't
always help unless you do something like you describe above.

But the WHERE clause on partial indexes is another story. The optimizer
recognizes IS NULL as being equivalent to IS NULL so it recognizes that the
partial index is usable just fine.

-- 
greg


In response to

pgsql-hackers by date

Next:From: Tom LaneDate: 2005-01-14 05:15:28
Subject: Re: Slow PL/pgSQL 8.0.RC5 (7.4.6. 3times faster)
Previous:From: Ron MayerDate: 2005-01-14 04:24:31
Subject: Re: Top-k optimizations?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group