From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Andreas Joseph Krogh <andreak(at)officenet(dot)no> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: PG won't use index on ORDER BY <expression> |
Date: | 2007-08-09 23:35:08 |
Message-ID: | 3878.1186702508@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2007-08-10 01:35:13 | Re: PG won't use index on ORDER BY <expression> |
Previous Message | Andreas Joseph Krogh | 2007-08-09 22:15:33 | Re: PG won't use index on ORDER BY <expression> |