Re: Useless index

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)

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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026