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

Le vendredi 8 juin 2012 15:57:07, Tom Lane a écrit :

> Cédric Villemain <cedric@2ndquadrant.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