Re: Need to increase performance of a query

Kenneth Marshall wrote:
> On Thu, Jun 10, 2010 at 12:34:07PM -0700, 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?
>>
>> Thanks,
>> Anne
>>
>>
> What about an IS NULL index on pf_id?
>
> Regards,
> Ken
>
Hi Ken,
I have the following index:
"item_rank_index2" btree (project_id) WHERE (pf_id IS NULL)

Are you suggesting something else?
Thanks,
Anne