Re: Useless index

On Thu, 14 Feb 2002, Tom Lane wrote:

> 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
>

Thanks,
    That did it.  I tried the explain with "score desc, bazid desc"
and it looked the same to me.  I had myself convinced it had to be that
way.  Then I tried your way (after I realized I had already selected a
specific bazid so it didn't matter if it was descending :), and it was all
okay.

 -brian

Wm. Brian McCane                    | Life is full of doors that won't open
Search http://recall.maxbaud.net/   | when you knock, equally spaced amid those
Usenet http://freenews.maxbaud.net/ | that open when you don't want them to.
Auction http://www.sellit-here.com/ | - Roger Zelazny "Blood of Amber"