Re: PG won't use index on ORDER BY <expression>

Andreas Joseph Krogh <andreak(at)officenet(dot)no> writes:
>> Another question then: Why doesn't "varchar_pattern_ops" handle ordering?

It does handle ordering, just not the ordering you're asking for here.
If you substituted USING ~<~ for ASC you'd find that the pattern_ops
index could be used for that.

>> This means I need 2 indexes on the columns I want to match with LIKE and
>> ORDER BY. Just doesn't seem right to need 2 "similar" indexes...

If you want to use the same index for both, you have to run the database
in C locale. Non-C locales generally define a sort ordering that is not
compatible with LIKE searches. (The point of the pattern_ops opclass is
really to force C-locale ordering of the index when the ordinary text
comparison operators yield a different ordering.)

> -- This doesn't
> EXPLAIN ANALYZE select firstname, lastname from person order by
> concat_lower(firstname, lastname) ASC, created DESC limit 10;

This ORDER BY is asking for an ordering that is almost completely
unrelated to the index's ordering.

regards, tom lane