Re: Using index with order desc

From: "Peter Gibbs" <peter(at)emkel(dot)co(dot)za>
To: "Dave Smith" <dave(dot)smith(at)candata(dot)com>, <pgsql-general(at)postgresql(dot)org>
Subject: Re: Using index with order desc
Date: 2002-07-17 12:22:57
Message-ID: 001701c22d8c$af6b2060$0b01010a@emkel.co.za
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

"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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Ralph Graulich 2002-07-17 12:30:42 max() not using index
Previous Message Richard Huxton 2002-07-17 11:18:14 Re: Blank date field.. Help...