Re: Need to increase performance of a query

От: Kenneth Marshall
Тема: Re: Need to increase performance of a query
Дата: ,
Msg-id: 20100610193712.GL3063@aart.is.rice.edu
(см: обсуждение, исходный текст)
Ответ на: Re: Need to increase performance of a query  (Anne Rosset)
Ответы: Re: Need to increase performance of a query  (Anne Rosset)
Список: pgsql-performance

Скрыть дерево обсуждения

Need to increase performance of a query  (Anne Rosset, )
 Re: Need to increase performance of a query  (Jesper Krogh, )
  Re: Need to increase performance of a query  (Anne Rosset, )
   Re: Need to increase performance of a query  (Jochen Erwied, )
 Re: Need to increase performance of a query  (hubert depesz lubaczewski, )
 Re: Need to increase performance of a query  (Anne Rosset, )
  Re: Need to increase performance of a query  (Kenneth Marshall, )
   Re: Need to increase performance of a query  (Anne Rosset, )
  Re: Need to increase performance of a query  (Jochen Erwied, )
   Re: Need to increase performance of a query  (Anne Rosset, )
  Re: Need to increase performance of a query  (Craig James, )
   Re: Need to increase performance of a query  (Anne Rosset, )
    Re: Need to increase performance of a query  (Joe Conway, )
     Re: Need to increase performance of a query  (Joe Conway, )
      Re: Need to increase performance of a query  (Anne Rosset, )
       Re: Need to increase performance of a query  (Joe Conway, )
        Re: Need to increase performance of a query  (Anne Rosset, )
   Re: Need to increase performance of a query  (Heikki Linnakangas, )
    Re: Need to increase performance of a query  (Anne Rosset, )
     Re: Need to increase performance of a query  (Heikki Linnakangas, )

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


В списке pgsql-performance по дате сообщения:

От: Robert Haas
Дата:
Сообщение: Re: slow query performance
От: Andy Colson
Дата:
Сообщение: Re: Analysis Function