Message-ID: <4522C8D8.4010601@refractions.net>
Date: Tue, 03 Oct 2006 13:32:24 -0700
From: Graham Davis <gdavis@refractions.net>
Reply-To: gdavis@refractions.net
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US;
	rv:1.6b) Gecko/20031205 Thunderbird/0.4
MIME-Version: 1.0
To: Chris Browne <cbbrowne@acm.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: BUG #2658: Query not using index
References: <200609272056.k8RKuWjY040283@wwwmaster.postgresql.org>
	<20061003000147.GA31475@wolff.to>
	<4522AA01.4020709@refractions.net>
	<60irj1gr3r.fsf@dba2.int.libertyrms.com>
	<4522B5B9.2050206@refractions.net>
	<60d599gqcz.fsf@dba2.int.libertyrms.com>
In-Reply-To: <60d599gqcz.fsf@dba2.int.libertyrms.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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@refractions.net


Chris Browne wrote:

>gdavis@refractions.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.
>  
>