From: Graham Davis <gdavis@refractions.net>
To: Chris Browne <cbbrowne@acm.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: BUG #2658: Query not using index
Date: Tue, 03 Oct 2006 12:13:43 -0700
Message-ID: <4522B667.3060002@refractions.net> (raw)
In-Reply-To: <60irj1gr3r.fsf@dba2.int.libertyrms.com>
References: <200609272056.k8RKuWjY040283@wwwmaster.postgresql.org>
<20061003000147.GA31475@wolff.to>
<4522AA01.4020709@refractions.net>
<60irj1gr3r.fsf@dba2.int.libertyrms.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: gdavis@refractions.net, cbbrowne@acm.org
Subject: Re: BUG #2658: Query not using index
In-Reply-To: <4522B667.3060002@refractions.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox