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

Search Discussions

  • Cédric Villemain at Jun 8, 2012 at 11:52 am

    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
  • Tom Lane at Jun 8, 2012 at 1:57 pm

    =?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
  • Cédric Villemain at Jun 8, 2012 at 2:31 pm

    Le vendredi 8 juin 2012 15:57:07, Tom Lane a écrit :
    =?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 ...
    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-performance @
categoriespostgresql
postedJun 8, '12 at 11:11a
activeJun 8, '12 at 2:31p
posts4
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase