Re: Useless index

On Thu, 14 Feb 2002, Tom Lane wrote:

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

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"