Обсуждение: How planner decides left-anchored LIKE can use index

Exactly when does the planner decide that a left-anchored like can use the
index?

I have replaced a WHEN lower(last_name) = 'smith'
with WHEN lower(last_name) like 'smith%'

There is an index on lower(last_name). I have seen the planner convert the
LIKE to lower(last_name) >= 'smith' and lower(last_name) < 'smiti' on 8.2.4
systems, but a slow sequence scan and filter on 8.1.9 - is this related to
the version difference (8.1.9 vs 8.2.4) or is this related to something like
operators/classes that have been installed?

Carlo

"Carlo Stonebanks" <stonec.register@sympatico.ca> writes:
> There is an index on lower(last_name). I have seen the planner convert the
> LIKE to lower(last_name) >= 'smith' and lower(last_name) < 'smiti' on 8.2.4
> systems, but a slow sequence scan and filter on 8.1.9 - is this related to
> the version difference (8.1.9 vs 8.2.4) or is this related to something like
> operators/classes that have been installed?

Most likely you used C locale for the 8.2.4 installation and some other
locale for the other one.

In non-C locale you can still get the optimization if you create an
index using the text_pattern_ops opclass ... but beware that this index
is useless for the normal locale-aware operators.

            regards, tom lane