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

"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

On 16 Jul 2002 Dave Smith wrote:

CREATE INDEX transfer_5 on transfer(company_id,module);

[snip]

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

[snip]

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)

yes, similiar problem once hit me as well... try:

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

Regards
--
Helge Bahmann <bahmann@math.tu-freiberg.de> /| \__
The past: Smart users in front of dumb terminals /_|____\
_/\ | __)
$ ./configure \\ \|__/__|
checking whether build environment is sane... yes \\/___/ |
checking for AIX... no (we already did this) |

On 16 Jul 2002 16:35:18 -0400, Dave Smith <dave.smith@candata.com>
wrote:

CREATE INDEX transfer_5 on transfer(company_id,module);

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

Dave,
...
ORDER BY company_id DESC, module DESC
^^^^

*might* help.

Servus
Manfred