Re: Determining which index to create

Martijn van Oosterhout <kleptog@svana.org> writes:
> That's wrong. It doesn't seem to realise that a reverse scan on the index
> would give the right answer. Note that that's only true because you're
> selecting only a single motid.

Yeah, there's currently little connection between the planner's code that
recognizes "you could use this index to meet these WHERE conditions"
and the code that recognizes "you could use this index to produce this
sort ordering".  In particular it has no clue that exact equality
constraints on earlier index columns might allow it to consider the
indexscan result as being sorted by later index columns.

> Not sure how to fix this though.

Offhand it doesn't seem like a trivial change :-(

I'd be wary of expending a lot of planning cycles to detect this,
because it doesn't seem like a very common case.  But if we can find
a way to do it cheaply, it'd be a cool optimization.

            regards, tom lane