Re: non index use on LIKE on a non pattern string

Le vendredi 8 juin 2012 15:57:07, Tom Lane a écrit :
> =?iso-8859-1?q?C=E9dric_Villemain?= <cedric(at)2ndquadrant(dot)com> writes:
> >> I have noticed that with a SELECT query containing the following
> >> constraint:
> >>
> >> column LIKE ?
> >>
> >> and an index on that column, PostgreSQL will not use the index
> >> even if the parameter doesn't contain special pattern characters
> >> such as %.
> >
> > you should have a postgresql 8.3,isn't it ?
> >
> > like is equal to "=" in your case, since 8.4
>
> No, the planner has understood about wildcard-free LIKE patterns
> producing an "=" index condition at least since 7.3. I think what the
> OP is complaining about is the problem that the pattern has to be
> actually constant (ie, NOT a parameter) before it can be optimized into
> an index condition. This should be better in 9.2 ...

Oops, maybe I shuffled with this
* xxx_pattern_ops indexes can now be used for simple equality comparisons,
not only for LIKE (Tom)

--
Cédric Villemain +33 (0)6 20 30 22 52
http://2ndQuadrant.fr/
PostgreSQL: Support 24x7 - Développement, Expertise et Formation