Message-ID: <4522B5B9.2050206@refractions.net>
Date: Tue, 03 Oct 2006 12:10:49 -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>
In-Reply-To: <60irj1gr3r.fsf@dba2.int.libertyrms.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

The asset_positions table has about 1.7 million rows, and this query 
takes over 40 seconds to do a sequential scan.  Initially I was trying 
to get the original query:

SELECT assetid, max(ts) AS ts 
FROM asset_positions 
GROUP BY assetid;

to use the multikey index since I read that PostgreSQL 8 added support 
for aggregates to use indexes.  However, the GROUP BY was causing the query
plan to not use any index (removing the GROUP by allowed the query to 
use the ts index and it took only 50 ms to run).  Since I need the query 
to find the max time
for EACH asset, I can't just drop the GROUP BY from my query.  So I was 
trying some alternate ways of writing the query (as described in the 
below email) to
force the use of one of these indexes.

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?

Graham.


Chris Browne wrote:

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