Re: How planner decides left-anchored LIKE can use index

"Carlo Stonebanks" <stonec(dot)register(at)sympatico(dot)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