Re: Useless index

Brian McCane <bmccane@mccons.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