From: Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
To: Anne Rosset <arosset@collab.net>
Cc: Craig James <craig_james@emolecules.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Need to increase performance of a query
Date: Thu, 10 Jun 2010 23:59:54 +0300
Message-ID: <4C11524A.9090702@enterprisedb.com> (raw)
In-Reply-To: <4C11463B.7080608@collab.net>
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: heikki.linnakangas@enterprisedb.com, arosset@collab.net, craig_james@emolecules.com
Subject: Re: Need to increase performance of a query
In-Reply-To: <4C11524A.9090702@enterprisedb.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox