Andreas Joseph Krogh <andreak@officenet.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