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