Re: BUG #2658: Query not using index

От: Graham Davis
Тема: Re: BUG #2658: Query not using index
Дата: ,
Msg-id: 4522C8D8.4010601@refractions.net
(см: обсуждение, исходный текст)
Ответ на: Re: BUG #2658: Query not using index  (Chris Browne)
Ответы: Re: BUG #2658: Query not using index  (Tom Lane)
Список: pgsql-performance

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

Re: BUG #2658: Query not using index  (Graham Davis, )
 Re: BUG #2658: Query not using index  (Chris Browne, )
  Re: BUG #2658: Query not using index  (Graham Davis, )
  Re: BUG #2658: Query not using index  (Graham Davis, )
   Re: BUG #2658: Query not using index  (Bruno Wolff III, )
 Re: BUG #2658: Query not using index  (Chris Browne, )
  Re: BUG #2658: Query not using index  (Graham Davis, )
   Re: BUG #2658: Query not using index  (Tom Lane, )
    Re: BUG #2658: Query not using index  (Graham Davis, )
     Re: BUG #2658: Query not using index  (Mark Lewis, )
      Re: BUG #2658: Query not using index  (Graham Davis, )
       Re: BUG #2658: Query not using index  (Mark Lewis, )
        Re: BUG #2658: Query not using index  (Graham Davis, )
         Re: BUG #2658: Query not using index  (Mark Lewis, )
          Re: BUG #2658: Query not using index  (Tom Lane, )
 PostgreSQL Caching  ("Adnan DURSUN", )
  Re: PostgreSQL Caching  ("Tomeh, Husam", )
   Re: PostgreSQL Caching  ("Adnan DURSUN", )
    Re: PostgreSQL Caching  ("Tomeh, Husam", )
     Re: PostgreSQL Caching  ("Adnan DURSUN", )
      Re: PostgreSQL Caching  ("Dave Dutcher", )
       Re: PostgreSQL Caching  (Brad Nicholson, )
     Re: PostgreSQL Caching  (Brad Nicholson, )

How come an aggreate like that has to use a sequential scan?  I know
that PostgreSQL use to have to do a sequential scan for all aggregates,
but there was support added to version 8 so that aggregates would take
advantage of indexes.   This is why

SELECT max(ts) AS ts
FROM asset_positions;

Uses an index on the ts column and only takes 50 milliseconds.  When I
added the group by it would not use a multikey index or any other
index.   Is there just no support for aggregates to use multikey
indexes?  Sorry to be so pushy, but I just want to make sure I
understand why the above query can use an index and the following can't:

SELECT assetid, max(ts) AS ts
FROM asset_positions
GROUP BY assetid;

--
Graham Davis
Refractions Research Inc.




Chris Browne wrote:

> (Graham Davis) writes:
>
>
>>40 seconds is much too slow for this query to run and I'm assuming
>>that the use of an index will make it much faster (as seen when I
>>removed the GROUP BY clause).  Any tips?
>>
>>
>
>Assumptions are dangerous things.
>
>An aggregate like this has *got to* scan the entire table, and given
>that that is the case, an index scan is NOT optimal; a seq scan is.
>
>An index scan is just going to be slower.
>
>




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

От: "Adnan DURSUN"
Дата:
Сообщение: Re: PostgreSQL Caching
От: "simon godden"
Дата:
Сообщение: simple case using index on windows but not on linux