Re: BUG #2658: Query not using index

От: Graham Davis
Тема: Re: BUG #2658: Query not using index
Дата: ,
Msg-id: 4522B667.3060002@refractions.net
(см: обсуждение, исходный текст)
Ответ на: Re: BUG #2658: Query not using index  (Chris Browne)
Ответы: Re: BUG #2658: Query not using index  (Bruno Wolff III)
Список: 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, )

Also, the multikey index of (assetid, ts) would already be sorted and
that is why using such an index in this case is
faster than doing a sequential scan that does the sorting afterwards.

Graham.


Chris Browne wrote:

> (Graham Davis) writes:
>
>
>>Adding DESC to both columns in the SORT BY did not make the query use
>>the multikey index.   So both
>>
>>SELECT DISTINCT ON (assetid) assetid, ts
>>FROM asset_positions ORDER BY assetid, ts DESC;
>>
>>and
>>
>>SELECT DISTINCT ON (assetid) assetid, ts
>>FROM asset_positions ORDER BY assetid DESC, ts DESC;
>>
>>use the same query plans and both do sequential scans without using
>>either the (assetid, ts) or (ts) indexes.  Any other ideas on how to
>>make this query use an index?  Thanks,
>>
>>
>
>Why do you want to worsen performance by forcing the use of an index?
>
>You are reading through the entire table, after all, and doing so via
>a sequential scan is normally the fastest way to do that.  An index
>scan would only be more efficient if you don't have enough space in
>memory to store all assetid values.
>
>


--
Graham Davis
Refractions Research Inc.




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

От: "Jim C. Nasby"
Дата:
Сообщение: Re: Forcing the use of particular execution plans
От: "Tomeh, Husam"
Дата:
Сообщение: Re: PostgreSQL Caching