Message-ID: <4522B667.3060002@refractions.net>
Date: Tue, 03 Oct 2006 12:13:43 -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=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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:

>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