Re: Useless index

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.

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"