Re: Useless index

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)

Yes, I suspected it was the mixing of non-DESC and DESC that caused the
index to be ignored.  The user knows the index can be used because the
have specified 'col = constant' but the index doesn't code doesn't have
those details.  Certainly a case of:

    col >= 10 AND col <= 11

would not allow a secondary DESC column to be used, while a secondary
non-DESC column would be fine.  I suppose there is no automatic fix we
can do here except to ask users to use matching DESC when they are
testing or a constant.

In fact, I am unsure why you are specifying the primary column in the
ORDER BY anyway if you know it will be a single value, except perhaps to
try and get it to use the index, right?

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