Message-ID: <4C11413B.8080107@emolecules.com>
Date: Thu, 10 Jun 2010 12:47:07 -0700
From: Craig James <craig_james@emolecules.com>
User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.6; en-US;
	rv:1.9.1.9) Gecko/20100317 Thunderbird/3.0.4
MIME-Version: 1.0
To: Anne Rosset <arosset@collab.net>
CC: 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>
In-Reply-To: <4C113E2F.6000305@collab.net>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 6/10/10 12:34 PM, Anne Rosset wrote:
> Jochen Erwied wrote:
>> Thursday, June 10, 2010, 8:36:08 PM you wrote:
>>
>>> psrdb=# (SELECT
>>> psrdb(# MAX(item_rank.rank) AS maxRank
>>> psrdb(# FROM
>>> psrdb(# item_rank item_rank
>>> psrdb(# WHERE
>>> psrdb(# item_rank.project_id='proj2783'
>>> psrdb(# AND item_rank.pf_id IS NULL
>>> psrdb(#
>>> psrdb(# )
>>> psrdb-# ORDER BY
>>> psrdb-# maxRank DESC;
>>
>> Don't think it does really matter, but why do you sort a resultset
>> consisting of only one row?
>>
> Sorry, I should have removed the ORDER by (the full query has a union).
> So without the ORDER by, here are the results:
> psrdb=# SELECT
> psrdb-# MAX(item_rank.rank) AS maxRank
> psrdb-# FROM
> psrdb-# item_rank item_rank
> psrdb-# WHERE
> psrdb-# item_rank.pf_id='plan1408';
> maxrank
> -------------
> 20504000000
> (1 row)
>
> Time: 1.516 ms
> psrdb=# SELECT
> psrdb-# MAX(item_rank.rank) AS maxRank
> psrdb-# FROM
> psrdb-# item_rank item_rank
> psrdb-# WHERE
> psrdb-# item_rank.project_id='proj2783'
> psrdb-# AND item_rank.pf_id IS NULL;
> maxrank
> -------------
> 20200000000
> (1 row)
>
> Time: 13.177 ms
>
> Is there anything that can be done for the second one?

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.  You need to create an index that includes the "IS NULL" condition.

   create index item_rank_null_idx on item_rank(pf_id)
    where item_rank.pf_id is null;

Craig