From: Anne Rosset <arosset@collab.net>
To: pgsql-performance@postgresql.org
Subject: Need to increase performance of a query
Date: Thu, 10 Jun 2010 10:50:40 -0700
Message-ID: <4C1125F0.8090101@collab.net> (raw)
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
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: arosset@collab.net
Subject: Re: Need to increase performance of a query
In-Reply-To: <4C1125F0.8090101@collab.net>
* 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