Re: Need to increase performance of a query

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