From: Chris Browne <cbbrowne@acm.org>
Subject: Re: BUG #2658: Query not using index
Date: Tue, 03 Oct 2006 15:02:32 -0400
Organization: cbbrowne Computing Inc
Lines: 27
Message-ID: <60irj1gr3r.fsf@dba2.int.libertyrms.com>
References: <200609272056.k8RKuWjY040283@wwwmaster.postgresql.org>
	<20061003000147.GA31475@wolff.to>
	<4522AA01.4020709@refractions.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
User-Agent: Gnus/5.1007 (Gnus v5.10.7) XEmacs/21.4.19 (linux)
Cancel-Lock: sha1:xgBRbBKfwvhVcUDNVa6gnXEuwqI=
To: pgsql-performance@postgresql.org

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.
-- 
(reverse (concatenate 'string "gro.mca" "@" "enworbbc"))
http://www3.sympatico.ca/cbbrowne/emacs.html
Expect the unexpected.
-- The Hitchhiker's Guide to the Galaxy, page 7023