Re: 8.2 planner and "like"

"Angva" <angvaw(at)gmail(dot)com> writes:
> Our company's application runs searches with "like" where clauses, for
> example: "where id like '38F20A%'". This query once ran in under 10 ms,
> but since upgrading from 8.1.3 to 8.2.0, it now takes about 500 ms to
> run. The problem appears to be that the planner does not want to use an
> index since the upgrade, for "like" queries.

> We experimented with planner cost constants, and we even tried setting
> enable_seqscan to false. A seq scan was done every time.

If it won't do it even with seqscan off, then it can't, which most
likely means that you mistakenly picked a non-C locale for the new
installation. You can either fix that (requiring re-initdb :-() or
provide a "pattern_ops" index that's not subject to locale oddities.
Non-C locales tend to impose a sort ordering that's not suited for
LIKE pattern matching. See
http://www.postgresql.org/docs/8.2/static/indexes-opclass.html

regards, tom lane