Message-ID: <4C1125F0.8090101@collab.net>
Date: Thu, 10 Jun 2010 10:50:40 -0700
From: Anne Rosset <arosset@collab.net>
User-Agent: Thunderbird 2.0.0.24 (Windows/20100228)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Need to increase performance of a query
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi,
I have the following query  that needs tuning:

psrdb=# explain analyze (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;
                                                                       
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------- 

Sort  (cost=0.19..0.19 rows=1 width=0) (actual time=12.154..12.155 
rows=1 loops=1)
  Sort Key: ($0)
  Sort Method:  quicksort  Memory: 17kB
  InitPlan
    ->  Limit  (cost=0.00..0.17 rows=1 width=8) (actual 
time=12.129..12.130 rows=1 loops=1)
          ->  Index Scan Backward using item_rank_rank on item_rank  
(cost=0.00..2933.84 rows=17558 width=8) (actual time=12.126..12.126 
rows=1 loops=1)
                Filter: ((rank IS NOT NULL) AND (pf_id IS NULL) AND 
((project_id)::text = 'proj2783'::text))
  ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual 
time=12.140..12.142 rows=1 loops=1)
Total runtime: 12.206 ms
(9 rows)

I have been playing with indexes but it seems that it doesn't make any 
difference. (I have created an index: item_rank_index" btree 
(project_id) WHERE (pf_id IS NULL))


Any advice on how to make it run faster?

Thanks a lot,
Anne