Re: BUG #2658: Query not using index

От: Graham Davis
Тема: Re: BUG #2658: Query not using index
Дата: ,
Msg-id: 4522CD8C.1000108@refractions.net
(см: обсуждение, исходный текст)
Ответ на: Re: BUG #2658: Query not using index  (Tom Lane)
Ответы: Re: BUG #2658: Query not using index  (Mark Lewis)
Список: 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, )

Thanks Tom, that explains it and makes sense.  I guess I will have to
accept this query taking 40 seconds, unless I can figure out another way
to write it so it can use indexes.  If there are any more syntax
suggestions, please pass them on.  Thanks for the help everyone.

Graham.


Tom Lane wrote:

>Graham Davis <> writes:
>
>
>>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.
>>
>>
>
>Not in a GROUP BY context, only for the simple case.  Per the comment in
>planagg.c:
>
>     * We don't handle GROUP BY, because our current implementations of
>     * grouping require looking at all the rows anyway, and so there's not
>     * much point in optimizing MIN/MAX.
>
>The problem is that using an index to obtain the maximum value of ts for
>a given value of assetid is not the same thing as finding out what all
>the distinct values of assetid are.
>
>This could possibly be improved but it would take a considerable amount
>more work.  It's definitely not in the category of "bug fix".
>
>            regards, tom lane
>
>


--
Graham Davis
Refractions Research Inc.




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

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