Re: BUG #2658: Query not using index

[Graham Davis <gdavis(at)refractions(dot)net>]

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.
gdavis(at)refractions(dot)net



Chris Browne wrote:

> gdavis(at)refractions(dot)net (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.
>