Using index with order desc

From: Dave Smith <dave(dot)smith(at)candata(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Using index with order desc
Date: 2002-07-16 20:35:18
Message-ID: 1026851718.4483.56.camel@playpen
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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)

Other info ..

select count(*) from transfer where company_id=1012;
count
-------
5264
(1 row)

import=# select count(*) from transfer;
count
-------
23481
(1 row)

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?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jeff Davis 2002-07-16 20:40:39 Re: no keys...
Previous Message Robert Treat 2002-07-16 19:45:08 Re: table size growing out of control