Re: Need to increase performance of a query

От: Anne Rosset
Тема: Re: Need to increase performance of a query
Дата: ,
Msg-id: 4C1140B6.5020803@collab.net
(см: обсуждение, исходный текст)
Ответ на: Re: Need to increase performance of a query  (Jochen Erwied)
Список: 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, )

Jochen Erwied wrote:
> Thursday, June 10, 2010, 9:34:07 PM you wrote:
>
>
>> Time: 1.516 ms
>>
>
>
>> Time: 13.177 ms
>>
>
> I'd suppose the first query to scan a lot less rows than the second one.
> Could you supply an explained plan for the fast query?
>
>
Hi Jochen,
Here is the explained plan for the fastest query:
psrdb=# explain analyze ELECT
psrdb-#        MAX(item_rank.rank) AS maxRank
psrdb-# FROM
psrdb-#        item_rank item_rank
psrdb-# WHERE
psrdb-#        item_rank.pf_id='plan1408';
ERROR:  syntax error at or near "ELECT" at character 17
psrdb=# explain analyze SELECT
psrdb-#        MAX(item_rank.rank) AS maxRank
psrdb-# FROM
psrdb-#        item_rank item_rank
psrdb-# WHERE
psrdb-#        item_rank.pf_id='plan1408';
                                                           QUERY PLAN

--------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=8.28..8.29 rows=1 width=8) (actual time=0.708..0.709
rows=1 loops=1)
   ->  Index Scan using item_rank_pf on item_rank  (cost=0.00..8.27
rows=1 width=8) (actual time=0.052..0.407 rows=303 loops=1)
         Index Cond: ((pf_id)::text = 'plan1408'::text)
 Total runtime: 0.761 ms
(4 rows)

Time: 2.140 ms



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

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