Message-ID: <4C113098.5040105@collab.net>
Date: Thu, 10 Jun 2010 11:36:08 -0700
From: Anne Rosset <arosset@collab.net>
User-Agent: Thunderbird 2.0.0.24 (Windows/20100228)
MIME-Version: 1.0
To: Jesper Krogh <jesper@krogh.cc>
CC: pgsql-performance@postgresql.org
Subject: Re: Need to increase performance of a query
References: <4C1125F0.8090101@collab.net> <4C112B19.6080908@krogh.cc>
In-Reply-To: <4C112B19.6080908@krogh.cc>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Jesper Krogh wrote:
> On 2010-06-10 19:50, Anne Rosset wrote:
>> Any advice on how to make it run faster?
>
> What timing do you get if you run it with \t (timing on) and without 
> explain analyze ?
>
> I would be surprised if you can get it much faster than what is is.. I 
> may be that a
> significant portion is "planning cost" so if you run it a lot you 
> might benefit from
> a prepared statement.
>
>
Hi Jesper,
Thanks your response:
psrdb=# \timing
Timing is on.
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;
   maxrank
-------------
 20200000000
(1 row)

Time: 12.947 ms

It really seems to me that it should take less time.

 Specially when I see the result  with a different where clause like 
this one:
psrdb=# SELECT
psrdb-#        MAX(item_rank.rank) AS maxRank
psrdb-# FROM
psrdb-#        item_rank item_rank
psrdb-# WHERE
psrdb-#        item_rank.pf_id='plan1408'
psrdb-# ORDER BY
psrdb-# maxRank DESC;
   maxrank
-------------
 20504000000
(1 row)

Time: 2.582 ms


Thanks,
Anne