Re: BUG #2658: Query not using index

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

Hi,

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
otherideas on how to make this query use an index?  Thanks, 

--
Graham Davis
Refractions Research Inc.




>On Wed, Sep 27, 2006 at 20:56:32 +0000,
>  Graham Davis <> wrote:
>
>
>>SELECT assetid, max(ts) AS ts
>>FROM asset_positions
>>GROUP BY assetid;
>>
>>I have an index on (ts), another index on (assetid) and a multikey index on
>>(assetid, ts).  I know the assetid index is pointless since the multikey one
>>takes its place, but I put it there while testing just to make sure.  The
>>ANALYZE EXPLAIN for this query is:
>>
>>                                                             QUERY PLAN
>>----------------------------------------------------------------------------
>>-------------------------------------------------------------
>> HashAggregate  (cost=125423.96..125424.21 rows=20 width=12) (actual
>>time=39693.995..39694.036 rows=20 loops=1)
>>   ->  Seq Scan on asset_positions  (cost=0.00..116654.64 rows=1753864
>>width=12) (actual time=20002.362..34724.896 rows=1738693 loops=1)
>> Total runtime: 39694.245 ms
>>(3 rows)
>>
>>You can see it is doing a sequential scan on the table when it should be
>>using the (assetid, ts) index, or at the very least the (ts) index.  This
>>query takes about 40 seconds to complete with a table of 1.7 million rows.
>>I tested running the query without the group by as follows:
>>
>>
>
>
>
>>SELECT DISTINCT ON (assetid) assetid, ts
>>FROM asset_positions
>>ORDER BY assetid, ts DESC;
>>
>>
>
>This is almost what you want to do to get an alternative plan. But you
>need to ORDER BY assetid DESC, ts DESC to make use of the multicolumn
>index. If you really need the other output order, reverse it in your
>application or use the above as a subselect in another query that orders
>by assetid ASC.
>
>




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

От: Ron Mayer
Дата:
Сообщение: Re: Forcing the use of particular execution plans
От: "Jim C. Nasby"
Дата:
Сообщение: Re: Forcing the use of particular execution plans