Re: Useless index

Brian McCane wrote:
> On Thu, 14 Feb 2002, Tom Lane wrote:
>
> > Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > > 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?
> >
> > Exactly. The sort ordering of the index is (col1,col2) while the
> > query as originally written wanted an ordering of (col2 desc).
> > The planner's not smart enough to realize that since the WHERE
> > constrains col1 to a single value, you could pretend the query
> > requests an ordering of (col1 desc, col2 desc) which is compatible
> > with the index.
> >
> > regards, tom lane
> >
>
> The annoying thing is that it is smart enough to get the correct index, I
> have another index that has the right col1, but different col2. It seems
> it should be possible to have the optimizer leave col1 in a "high
> impedence" state, and then allow col2 to make a decision. I have not
> looked at the code, but if we have a known value for col1 as a constant,
> I would think we could let subsequent information make the decision and
> then just bubble it up to the top.

Tom, do we have a TODO item here?

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