Обсуждение: 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"

Brian McCane <bmccane@mccons.net> writes:
> Then I tried your way (after I realized I had already selected a
> specific bazid so it didn't matter if it was descending :),

Yup, that's the little flash of insight that the planner is incapable
of having :-(

            regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Tom, do we have a TODO item here?
>
> I'm not excited about it; it seems a weird case and there's a simple
> query change to get the right thing to happen.  AFAICS we'd have to
> expend a lot of planner cycles to detect this case, and most of the
> time those cycles would be wasted.

Yes, however, in our case the TODO list or FAQ does show tips or
limitations of our existing code.  Not sure either.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026