Re: Using index with order desc

"Dave Smith" wrote:


> Using pg 7.2.1
>
> If I have a simple table transfer
>
> company_id int,
> module character(2)
> otherfields ...
>
> and an index
> CREATE INDEX transfer_5 on transfer(company_id,module);
>
> with the query
>
> explain select * from transfer where company_id=1012 order by
> company_id,module desc;
>
> I get ..
>
> NOTICE:  QUERY PLAN:
>
> Sort  (cost=462.74..462.74 rows=117 width=176)
>   ->  Index Scan using transfer_5 on transfer  (cost=0.00..458.71
> rows=117 width=176)
>
>
> Why does it have to sort it?  The desc is at the end and the first
> argument is constant. Is there some way I can force postgres to read the
> index backwards and save the sort step?
>

Try:

explain select * from transfer
  where company_id=1012
  order by company_id desc, module desc;

--
Peter Gibbs
EmKel Systems