Re: Useless index

Brian McCane wrote:
> On Thu, 14 Feb 2002, Tom Lane wrote:
>
> > Bruce Momjian <pgman@candle.pha.pa.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@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