Re: Useless index

Brian McCane <bmccane(at)mccons(dot)net> writes:
> CREATE INDEX foo_index ON foo (bazid, score desc) ;

> Which would be exactly what I want, and would complete in a split second.
> Instead, this thing runs FOREVER (okay, it just seems that way to my
> client :). Is there any way to get the equivalent index from PostgreSQL?

You don't need a funny index, you just need to get the planner to notice
that that index can serve to create the desired output ordering. Try

create table foo(bazid int, score int);
CREATE INDEX foo_index ON foo (bazid, score) ;

explain select * from foo where bazid = 123456
order by bazid desc, score desc limit 100 ;

NOTICE: QUERY PLAN:

Limit (cost=0.00..17.07 rows=5 width=8)
-> Index Scan Backward using foo_index on foo (cost=0.00..17.07 rows=5 width=8)

EXPLAIN

regards, tom lane