Re: Index ot being used

> John A Meinel <john(at)arbash-meinel(dot)com> wrote:
>> I don't know if there are specific reasons why not, other than just not
>> being implemented yet. It might be tricky to get it correct

Not so much tricky to get correct, as potentially expensive to test for;
it'd be quite easy to waste a lot of cycles trying to match ORDER BY
keys in multiple ways to completely-irrelevant indexes. Since this
will only be helpful for a minority of queries but the costs would be
paid on almost everything with an ORDER BY, that consideration has been
looming large in my mind.

Bruno Wolff III <bruno(at)wolff(dot)to> writes:
> I think you need to be able to do two things. One is to drop constant
> expressions from order by lists. The other is when looking for an index
> to produce a specific ordering, to ingore leading constant expressions
> when comparing to the order by expressions.

I've been thinking about this some more this morning, and I think I see
how it could be relatively inexpensive to recognize x=constant
restrictions that allow ordering columns of an index to be ignored. We
are already doing 90% of the work for that just as a byproduct of trying
to match the x=constant clause to the index in the first place, so it's
mostly a matter of refactoring the code to allow that work to be reused.

I don't, however, see an equally inexpensive way to ignore ORDER BY
columns. That would imply associating the '=' operator of the
restriction clause with the '<' or '>' operator of the ORDER BY clause,
which means searching for a btree opclass that has them in common, which
is not cheap since there's no indexing on pg_amop that would allow us to
find it easily. (There are various places where we do in fact do that
sort of thing, but they aren't so performance-critical.) This doesn't
come up in the other case because we already know the relevant opclass
from the index.

I don't think the use-case has been shown that justifies doing this much
work to ignore useless ORDER BY clauses. The examples that have come up
in the past all suggest ignoring index columns not the other way 'round.
Can you make a case that we need to do that part of it?

regards, tom lane