Обсуждение: non index use on LIKE on a non pattern string

Hello,

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 %.

From PG POV it might be logical, because, who is stupid enough to
use the LIKE operator if it's unneeded, right?

However from my application POV the users sometimes want to
provide a pattern with % and sometimes a more precise condition,
and of course, I am uneasy at writing two very similar SQL
requests with only the LIKE/= difference; in the end, the non use
of an index means unwanted performance degradation.

I have come with the following hack in the SQL:

       ( position('%' in ?) > 0 OR column = ? )
       AND ( position('%' in ?) = 0 OR column LIKE ? )

(I know it doesn't cover all the pattern possibilities)

Any thoughts on what would be the best approach? Mine looks a bit
ugly.

Thanks,

--
Guillaume Cottenceau

> 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

Also you probably want to have a look at
 http://www.postgresql.org/docs/9.1/static/indexes-opclass.html
about your index definition (add the "text_pattern_ops" when required)

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

=?iso-8859-1?q?C=E9dric_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 ...

            regards, tom lane

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