Message-ID: <4C11524A.9090702@enterprisedb.com>
Date: Thu, 10 Jun 2010 23:59:54 +0300
From: Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
Organization: EnterpriseDB
User-Agent: Mozilla/5.0 (X11; U; Linux i686; fi-FI;
	rv:1.9.1.9) Gecko/20100515 Iceowl/1.0b1 Icedove/3.0.4
MIME-Version: 1.0
To: Anne Rosset <arosset@collab.net>
CC: Craig James <craig_james@emolecules.com>, pgsql-performance@postgresql.org
Subject: Re: Need to increase performance of a query
References: <4C1125F0.8090101@collab.net> <4C112B19.6080908@krogh.cc>
	<4C113098.5040105@collab.net> <98414706.20100610212211@erwied.eu>
	<4C113E2F.6000305@collab.net> <4C11413B.8080107@emolecules.com>
	<4C11439C.3010603@enterprisedb.com> <4C11463B.7080608@collab.net>
In-Reply-To: <4C11463B.7080608@collab.net>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 10/06/10 23:08, Anne Rosset wrote:
> Heikki Linnakangas wrote:
>> On 10/06/10 22:47, Craig James wrote:
>>> Postgres normally doesn't index NULL values even if the column is
>>> indexed, so it has to do a table scan when your query includes an IS
>>> NULL condition.
>>
>> That was addressed in version 8.3. 8.3 and upwards can use an index
>> for IS NULL.
>>
>> I believe the NULLs were stored in the index in earlier releases too,
>> they just couldn't be searched for.
>>
> I am using postgres 8.3.6. So why doesn't it use my index?

Well, apparently the planner doesn't think it would be any cheaper.

I wonder if this helps:

CREATE INDEX item_rank_project_id ON item_rank(project_id, rank, pf_id);

And make sure you drop any of the indexes that are not being used, to 
make sure the planner doesn't choose them instead.

(You should upgrade to 8.3.11, BTW. There's been a bunch of bug-fixes 
in-between, though I don't know if any are related to this, but there's 
other important fixes there)

-- 
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com